's recent business needs more timely attention, so there is often a need to display the latest N days of data in the report. I have previously introduced the use of DAX to dynamically display the latest N days:
Power BI dynamic display The data of the last N days
If you only need to analyze the data of the last N days in the report, you don’t have to be so troublesome. You can also use the date filter of PowerQuery for more convenient implementation.
After importing the data into PowerQuery, filter the data according to the date field in the data. For example, in this order table, just click the drop-down arrow on the right side of the order date column to find the date filter:
There are various granular filters built into the date filter. , Such as yesterday, today, this week, last week, this month, this year, etc., you can even refine to filter rows by hour, minute, and second, and you can also customize the filter.
If you only need the last 5 days of order data, just select "before" and enter 5 days in the pop-up window:
and upload it,The data in the model is the last 5 days, which can be displayed directly.
The previous 5 days used above does not include today. If you want to include the latest 5 days data of today, you can set it like this: the previous 4 days and today.
If you plan to modify the data for the last 15 days, click "convert data" to enter the PowerQuery editor again, and click the filter row to enter the row gear next to the filter step again. Button, change 5 to 15 on it.
You can also directly modify the M code of this step, change the 5 of it to 15:
0p _p5 The most recent span parameter of Power span
and then modify the M code to:
img6 p0
img6 p0
'S screening has been in the last N days.
In this way, the data loaded into the model is always the last N days.It will dynamically change with the change of the date. It is simpler and lighter, but simple is also accompanied by monotony. It cannot flexibly display the last N days before a certain date based on user interaction, nor can it only use this table for other analysis. , Such as comparative analysis of the same period.
Which method is used and how to build a model is still measured according to actual business needs.
Of course, if you have a lot of historical data, and you only need to analyze the last 3 years, you can also use the date filter to dynamically extract the last 3 years of data without loading all the historical data to In the data model, this can significantly improve the performance of the model.
Regarding the PowerQuery date filter, you can try other granularities and filtering methods yourself.
More recommendations
My new book "PowerBI Business Data Analysis" is available
How does PowerBI conduct fiscal year analysis?
PowerBI is running slow? 15 best practices for improvement are given to you
Cai Wu, founder of "PowerBI Planet", author of "Power BI Business Data Analysis"
.-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-