Finding Max Date with List.Max() in Power Query to Create a Future Date

In the Contoso Retail database, we have old historical data which only goes up until the end of 2009. For our demo reports, we’d like to use more up-to-date data, so we want to add a date field to the FactOnlineSales (and FactSales) tables, so we can mimic more current reports.

To add what we’ll call a ‘future date’ field, we want to find out the number of days from the present day to the last date in the FactOnlineSales table, then we can recalculate all the old dates bringing them forward by this difference.

Getting the Max Date in Power Query

There doesn’t appear to be a direct M code function for max dates, but there is a List.Max() function which works with lists, so we need to create a list from the date field in the FactOnlineSales table. The steps are as follows:

1. Create a list from the date field (right-click on the DateKey field and select ‘Add as New Query’.

2. This will create a list of dates from – here I renamed it to FactOnlineSalesDates

3. We can now reference this list in the FactOnlineSales table using the List.Max() function.
So we need to create a new custom column in the FactOnlineSales table as follows:

We then just need to transform it into a Date type:

That will give us a column containing the max date (the last date), in the FactOnlineSalesTable. The MaxDate is 31/12/2009. We can now use that data in our calculations. Sure we could have just looked for it, but if the data is dynamic, it’s good to be able to calculate it, so we are sure it is correct.

4. Next we can add another custom column containing today’s date and transform it to a Date type as well. We use the Date.From() function, working on the DateTime.LocalNow() function:

5. Next add another custom column to calculate the date difference, between today and the max date in the FactOnlineSales table. We can use the Duration.Days() function to calculate the difference in days between today and the last date in the FactOnlineSales table.

This gives us the number we are looking for.

Now all we need to do is add another custom column to transform the dates into the present day.
We can call this ‘DateFuture’. And we can transform it to Date type.


We’ve added 4 additional columns here to a big table. To limit the impact on performance, we should remove the 3 columns we don’t need, just leaving us with the DateFuture column.

Now close & apply the data from Power Query to load it into the model.
In the model view, we can now edit the relationship between the Date table and the FactOnlineSales table, so they are joined using the DateFuture key, which will bring all the data into the future, when we use filters from the Date table.

If we now look at the FactOlineSales table, we can see the latest date is today (at the time of writing).


In practice a data warehouse is updated overnight, so the latest complete days worth of data should be the day before, so we want to tweak our calculation.

If we go back into Power Query query we can subtract a day from today’s date, using the Date.AddDays()function, so this will feed into the future date calculation.
Date.AddDays(Date.From(DateTime.LocalNow()), -1)


Now we see the data is up to today.

So, this is useful if we want to mimic a real-life scenario, where data is up until yesterday relative to today, but it will be confusing for financial reports if our future date keeps changing, so it makes sense to have a fixed date as well.

So to fix the today’s date field, we can do the following, using the #date function




Comments

Leave a Reply

Your email address will not be published. Required fields are marked *