often encounters such a requirement. Set a slicer in the report. When the user opens the report, the data of the latest period is displayed by default. For example, when the report is opened on November 30, 2020, the display is November 2020. Monthly data, open the refresh again the next day, and automatically display the data of December 2020.
At present, PowerBI has no way to dynamically change the options of the slicer. When you select a specific period, the next time you open it, it will still be the same period, and it cannot be automatically changed to the latest.
This requirement is very common, and many people are troubled by this. Many people put forward this requirement in the Microsoft PowerBI community a few years ago, but after so many years, the current version still does not have this function, then we pass a Work around with to achieve it. The ultimate purpose of
is to dynamically filter data. Since the options of the slicer cannot be changed automatically, then change the idea , and the interval represented by the option can be made dynamic , take the display of this month's data as an example, only You need to add an calculated column to the date table:
year month =
IF([ year month]=FORMAT(TODAY(),"YYYYMM"),"this month",[year month])
The meaning of in this column is , if the current year month is all in the year month, return this month, otherwise return the value of the year month normally, the result is as follows:
Then use this column to make a slicer.
Because the "this month" in the calculation column will be dynamically calculated according to the value of TODAY, so any time you open this report and refresh, the data of "this month" will be displayed by default, thus realizing the automatic screening of in of the report. Another common situation in
is to dynamically display the data of the last day of the business . After each opening and refresh, the report automatically displays the data of the last day without selecting a slicer. In the same way as
, create a new column in the date table:
The meaning of this new column is that if it is less than the date of the last day of the business, the date will be returned normally. If it is equal to the date of the last day, it will return the "latest business date". The date of the last day, the null value BLANK is returned.
where MAXX(ALL('order table'),'order table'[order date]) is used to calculate the latest date in the order table, of course, you can also write this date as a measure first, and directly in the calculation column Citations are also possible. In this example
, the date of the last day of the business is December 8, 2020, and this column in the date table is displayed as follows:
If the business in the order table is updated to December 9, this column is automatically displayed on December 9 It is "Latest Business Date", so use this column as a slicer and select "Latest Business Date", the report can automatically switch data with the latest date in the order table.
Because there are empty values in this column, a BLANK option will be displayed in the slicer. If you do not want to display it, you can remove it in the filter.
Or use the built-in slicer, use the drop-down mode, and only display the option of the latest business date. In order to let the user know which day the latest business date is, you can put a card image in the report to display the date:
Of course, if If you want to select another date, just click on the slicer to select, and the user still has the freedom to view the data of other dates.
The above are solutions for two common needs. The options of the slicer are fixed, but the interval represented by this option is dynamic. In the same way, if your business needs are displayed as today, yesterday, last month, this year and other effects by default in the report, you can learn from this idea.
More recommended
Mindful@PowerBI Planet
the end
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-