Dynamically Display the Most Recent N Days using PQ

[v-alq-msft]

The blog describes an often met scenario that is when there is a set of data for yearly sales, recording daily revenue of a store. We want Power Bi to dynamically display the most recent data in the entire data, only displaying the dates of the last 7 or 30 days. It is usually recommended to use DAX method as below:

DAX:

 

Measure = CALCULATE(SUM([Sales]),FILTER('Table',[Date]>=MAXX(ALL('Table'),[Date])-7))

There might be other dax methods, but the idea is just writing a dax measure to filter out the most recent 7 days. However, the sales data table could record many previous years’ sales with thousands rows, which is very common in real world. If we only want the most recent data, it is not wise to load all data into data view to calculate with dax column or measures. Instead, we only need to load the subset of the most let’s say 14 days of the entire raw data. In this case, we must use Power Query.

Power Query:

Scenario 1:

In this table, there is just Date and Sales data of a store, if we just want sales after or equal to a specific date we could just filter the date.  

And if we want most recent 14 days’ sales. The simplest method is to sort the date in descending order and then use filter to display the recent 14 days based on an index column, as shown below. 

There are also other methods, for example we could use MaxN and in function to find whether the row is in the recent 14 days and display true and false in a custom column, then filter the True row to display the recent 14 days.

Scenario 2:

In the case a store has recorded sales for each item, and the owner wants the most recent 3 days’ sales of each item when the sales dates are differed among the 3 items. We could use Table.Group function that take the whole grouped table, sorts it and takes the most recent 3 rows using:

Table.FirstN(Table.Sort(_, {{"Date", Order.Descending}}),3)

With this approach, we are cleansing all data meaning we don't have to work with all data after the function is applied.

Scenario 3

In reality, it is not the best option to manually change the number in the code to return most N days. We want to filter the most recent N days and apply to data view in a more dynamic way. Therefore, we could take a benefit of the Query Parameter in PQ.

Before creating a parameter of most recent N days, we must create a list that allows us to use the parameter, so we would like to rank the date in descending order. Usually, we could just sort the date and add an index column, but we cannot do that in this sample with repeated date. Instead, we can group by and select All Roles for operation, then add an index after sort date in descending order. After expanding, the index should rank it correctly considering repeated date.

The final step is to use a parameter on the table. We first add the index column as a list of new query and remove duplicates, then use this query to create the parameter and apply it to filter the index column as below.

M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc7NDcAgCIDRXTibiPhHj3YN4/5rFNqLpuCFxC9PmRMICWOimCBARjkyB6xwFuKv3EchuUlsGS2ElslaqmXeQpYp22vjX0xTdbfLMtXdrWnJlmmu6e4/3TWspViGtw3ErAc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}, {"Item", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Table3", each _, type table [Date=nullable date, Sales=nullable number, Item=nullable text]}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Date", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Table3" = Table.ExpandTableColumn(#"Added Index", "Table3", {"Sales", "Item"}, {"Table3.Sales", "Table3.Item"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table3", each [Index] <= #"Most Recent N Days")
in
    #"Filtered Rows"

Now, we could use the Edit Parameters feature to filter the most recent N days and load them into Data view for further calculation. This purpose of this blog is to demonstrate how to filter the table to only display the recent data and prevent thousands rows load into data view, which may bring troubles to report designers.

Hope this article helps everyone with similar questions here.

Author: Paul Zheng

Reviewer: Ula Huang, Kerry Wang

Dynamically Display the Most Recent N Days using PQ.pbix