To introduce a rolling time window in Power BI that dynamically updates the visuals to represent data from the past 7 days or 30 days, there are DAX measures and date filter options that can be altered depending on the date today. Here is how to do it stepwise.
Create a Date Table: If your existing data model doesn't have a date table, you will have to create an appropriate date table to accommodate all time-related calculations. Ensure that it covers every possible date range without leaving out any gaps and comprises at least a column for day, month, and year. Such a date table is important for applying time range filters in Power BI.
Add a DAX measure for the Rolling Window: To visualize how figures change over the past 7 or 30 days, create another measure on two dimensions that calculate windows.
For example, if one wants to get a rolling 7-day measure, he can write the DAX expression as follows.
Rolling 7 Days Sales =
CALCULATE(
SUM('Sales'[Amount]),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY)
)
This metric finds the total sales for the last seven days, calculated from the maximum date in the current context. In the case of a rolling window of thirty days, you would modify the -7 to -30 to include every day for thirty days.
Making Use of Relative Date Filtering: Power BI also comes with an option that makes it easier to rotate and adjust the time periods by including relative date filters in the visual. Within the Filters pane, click on the Date field and select the “Relative date filtering” option. At this point, one can indicate “is in the last” and fill in the number of days (say 7 or 30) in order to restrict their visuals from showing any data older than a week or a month, respectively. This filter will refresh itself whenever new information is inputted or when the day of the data entry progresses, enabling the visual to be up to date with no need for manual intervention.
In addition to facilitating the automatic updates to the visuals within the reports over a run period, the date table and the DAX measures complemented by the relative date filtering make the Power BI report more engaging for purposes of continuous evaluation.
