Generate missing months within a date range

Power Query
1

Hi All,

I have a table in my data model that shows the monthly demand for each product over the last 12 months. For instance, as of today (11th Dec 2020), there should be demand from Dec 2019 through to November 2020. However, there are many products in my dataset that could have no demand for some months and, as a result, that month will be missed from the dataset. Is there a method in Power query to detect which months are missing from the dataset and add a new row with a demand quantity of zero?
A couple of things to consider - the storage mode has to be Direct Query, because the items can have demand in multiple warehouses and sites we have take that into account also. I have attached an Excel sheet as an example of the dataset…

Thanks in advance,

Pete.Generate missing dates.xlsx (293.9 KB)

2

Hi @pete962,

I hope this works for you.

First created a supporting query called: ListMonths for the dynamic date range.

let
    Today = Date.From(DateTime.FixedLocalNow()),
    FirstDate = Date.StartOfMonth( Date.AddMonths( Today, -12 )),
    LastDate = Date.StartOfMonth( Date.AddMonths( Today, -1 )),
    Source = List.Generate( ()=> FirstDate, each _ <= LastDate, each Date.AddMonths(_, 1))
in
    Source

and next for your monthly demand table

let
    Source = Excel.Workbook(File.Contents(FileLocation), null, true),
    tData_Table = Source{[Item="tData",Kind="Table"]}[Data],
    ChType = Table.TransformColumnTypes(tData_Table,{{"DATAAREAID", type text}, {"SITEID", type text}, {"WAREHOUSEID", type text}, {"DEMANDDATE", type date}, {"DEMANDQUANTITY", Int64.Type}, {"ITEMNUMBER", type text}, {"PARTITION", Int64.Type}, {"RECID", Int64.Type}}),
    GroupItem = Table.Group(ChType, {"ITEMNUMBER"}, {{"AllRows", each _, type table [DATAAREAID=nullable text, SITEID=nullable text, WAREHOUSEID=nullable text, DEMANDDATE=nullable date, DEMANDQUANTITY=nullable number, ITEMNUMBER=nullable text, PARTITION=nullable number, RECID=nullable number]}}),
    AddedCustom = Table.AddColumn(GroupItem, "Temp", each 
        if Table.RowCount([AllRows])=12 then [AllRows] else
        let
            myTable = [AllRows],
            GroupDemand = Table.Group(myTable, {"DEMANDDATE"}, {{"AllRows2", each _, type table [DATAAREAID=nullable text, SITEID=nullable text, WAREHOUSEID=nullable text, DEMANDDATE=nullable date, DEMANDQUANTITY=nullable number, ITEMNUMBER=nullable text, PARTITION=nullable number, RECID=nullable number]}}),
            AddRows = List.Difference(ListMonths, GroupDemand[DEMANDDATE]),
            ToTable = Table.FromList(AddRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            AddTables = Table.AddColumn(ToTable, "Custom", each #table( Table.ColumnNames(myTable), {})),
            MatchNames = Table.RenameColumns( AddTables, List.Zip( {Table.ColumnNames( AddTables ), Table.ColumnNames( GroupDemand)} )),
            CombineData = Table.Combine({GroupDemand,MatchNames} ),
            ExpandGroupDemand = Table.ExpandTableColumn(CombineData, "AllRows2", {"DATAAREAID", "SITEID", "WAREHOUSEID", "DEMANDQUANTITY", "ITEMNUMBER", "PARTITION", "RECID"}, {"DATAAREAID", "SITEID", "WAREHOUSEID", "DEMANDQUANTITY", "ITEMNUMBER", "PARTITION", "RECID"}),
            FillDown = Table.FillDown(ExpandGroupDemand,{"DATAAREAID", "SITEID", "WAREHOUSEID", "ITEMNUMBER", "PARTITION", "RECID"})
        in
            FillDown, type table [DATAAREAID=nullable text, SITEID=nullable text, WAREHOUSEID=nullable text, DEMANDDATE=nullable date, DEMANDQUANTITY=nullable number, ITEMNUMBER=nullable text, PARTITION=nullable number, RECID=nullable number] )[[Temp]],
    ExpandedTemp = Table.ExpandTableColumn(AddedCustom, "Temp", {"DATAAREAID", "SITEID", "WAREHOUSEID", "DEMANDDATE", "DEMANDQUANTITY", "ITEMNUMBER", "PARTITION", "RECID"}, {"DATAAREAID", "SITEID", "WAREHOUSEID", "DEMANDDATE", "DEMANDQUANTITY", "ITEMNUMBER", "PARTITION", "RECID"})
in
    ExpandedTemp

With this result.
You can easily replace the null with 0 should that be required.

image
image1314×347 10.1 KB

Here’s your sample file. eDNA - Generate Missing Months.pbix (89.0 KB)
I hope this is helpful.

1 Like
3

@pete962

Create date table in your model(if you don’t) and relationship between tables.
Then create visual Months from date table and measure like below

sum(demandquantity) + 0

image
image1205×786 49.9 KB

Generate missing months within a date range.pbix (81.6 KB)

2 Likes
4

Hi @Melissa,

This is excellent and it works perfectly and I will definitely be using this solution in other reports where the connection type is Import. Unfortunately, the solution does not seem to work for my current requirement due to the fact that it has to be a Direct Query report. I get this error when I attempt to apply the changes:

image .

A bit more detail about the requirement: I need to do a STDEVP dax measure against the Demand Quantity and I require the previous 12 months, otherwise the standard deviation calculation is incorrect.

Is there a way to achieve this through DAX or other transformations that would be suitable for DirectQuery?

Thanks again,

Pete.

5

Hi Rajesh,

Thanks for this.

Is this a DAX measure that I would be able to use in an STDEVP calculation?

Thanks,

Pete.

6

That is helpful to know. I would suggest closing this thread and creating a new one for that.
Just provide a sample PBIX and a mock-up of the disired results.

7

Thanks Melissa, I will do that, and thanks again for the original solution.