If you have ever performed temporal analysis in Power BI, you probably already know how useful it is to include a Calendar table to the data model. Whether you want to know the change in revenue compared to the same period of the previous year, or you want to represent data coming from different tables on the same time scale, adding a Calendar table to your model will be of great help, thanks to the powerful DAX date and time functions available in the Microsoft tool.
In addition, a Calendar table will allow you to add temporal filters at the page level to perform relative filtering of the data and display, for example, data from the last six months.
When a filter is applied at the page level, all visual objects on that page are synchronized with the same time interval.
Now, let's say you are in the month of June 2022 and you want to represent the latest sales data available up to that date (let's say it is updated up to May 2022). In this case, you would not be able to use a date relative filter at the page level, because you would only have the option to select the last month (June 2022) and the report would appear empty.
So how can we keep a report updated with the latest sales data available, without having to manually apply filters every time we open the report?
In this article we will see how to keep a Power BI report automatically updated with the latest available data:
Keep your Power BI reports automatically updated with the latest available data
Step 1: Create your Calendar table
As we have seen above, creating a Calendar table and adding it to your model provides numerous advantages. This step can be done using Power Query (M language) or by creating a new table with DAX functions. To create a new table using DAX, you should click on the "New table" tab in the upper panel and then type the following formula:
The CALENDAR (DAX) function allows you to create a table with a column containing all the dates between the intervals specified as parameters. In this example, we will create a table with data from the beginning of the millennium (January 1, 2000) until today.
There are other functions such as CALENDARAUTO that returns a table with a date column containing only the range of dates present in the data model automatically.
Step 2: Relate your Calendar table to the facts table
Now that you have created the dates dimension, you will need to relate it to your fact table (sales). To do this, click on the "Model" panel on the left side of Power BI and then on the "Manage relationships" tab.
Then, select the date columns you want to relate in both tables. In this example, the "DateKey" column of the Calendar table will be related to the "DateKey" column of the "Sales" table:
Step 3: Add a column to your Calendar table that identifies the date with the latest data available
Once you have created the Calendar table and linked it to the "Sales" fact table, you will need to create a new column in the Calendar table that identifies the last date in our fact table:
he specified formula will evaluate in each row of the Calendar table whether or not the value corresponds to the last date present in the "Sales" fact table, returning "True" when this condition is met and "False" when it is not.
Step 4: Apply the filter at the page level
Once you have created the Calendar table, related it to the facts table and identified the date with the last available data, you only have to apply a page-level filter by selecting the value "True" in the column you just created in the previous step:
This way you can keep your report automatically updated with the latest available data and, most interestingly, when the fact table is updated with more recent data, the report will be filtered showing the latest available data without the need for you to do it manually.
What did you think? If you have any questions or think we can help your organization, do not hesitate to contact us. We will be happy to help you😊