When we use Power BI to create charts, we often encounter the challenge: each time we review the data, we need to manually click on the filter to select the current month’s data. But in fact, there is a clever and convenient way to solve this problem. In this article, we will show you how to use some simple DAX functions to easily set up Power BI charts to automatically display the current month’s data.
Steps to Create a Default Date slicer
Step 1: Extract Year, Month, or Day from a Date in Power Query.
Step 2: Extract Month and Year from Datetime.
Step 3: Use the formula to merge the year and month.
- Year + Month = ‘Sales Report’ [Year] * 100 + ‘Sales Report’ [Month]
Step 4: Use Power BI DAX formulas to automatically display the current month’s data.
Power BI DAX formulas Introduction：
- IF function: Checks a condition, and returns one value when it’s TRUE, otherwise it returns a second value.
- AND function: Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns false.
- FORMAT function: Converts a value to text according to the specified format.
- DATE_CURRENTMONTH = IF(AND(‘ Sales Report’ [Year]=YEAR(TODAY()),’ Sales Report’ [Month] =MONTH(TODAY())),”Current Month”,FORMAT(‘ Sales Report’ [YearMonth],”general number”))
Chart Presentation <Method 1>
The cross-analysis filter is automatically preset to the current month, and when you click on the slicer, you can link to the data in the chart.
Chart Presentation <Method 2>
Insert a date filter on the visual page to have the chart automatically display as the current month’s data.
Once you’ve done above actions, it’s easy to have Power BI charts automatically display as current month’s data, and you’ll be able to analyze data more efficiently and make your workflow smoother. In addition, this technique of automatically displaying the data of the month not only improves the professionalism and immediacy of the report. It is also more suitable for use on situation dashboard, which can help decision-makers make decisions and analyze effectively, so let’s start this learning journey and explore Power BI!
KSCC is a management consulting company in Taiwan. Our services include corporate in-house training, consulting, and leadership management.For more information about our corporate services, please feel free to visit our website: https://kscthinktank.com.tw/custom-training/