Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to find the rows with missing date range

Hi Everyone,   We get the Bills from clients every month, but the Bill start date and Bill end dates are not constatnly the same. I would like to find the invoices missing between these dates. The c...
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Anonymous I am not aware of a way to do this completely in DAX. Some of it will need to be done in Power Query. Would that be ok?

 

Here's what I have done so far:

 

adjusted your source data for my locale

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BDYAwDAN3ybuSnYTSdJaq+68BFRAEz/Pdw2OIShE1sMJoXLCB+wWz3L6CLX0D4+cjpxMc7K+PFVBBfwJnwifIB9YT5jwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store code" = _t, #"Bill Start Date" = _t, #"Bill End date" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Start Date", each Date.FromText([Bill Start Date],"en-gb")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End Date", each Date.FromText([Bill End date],"en-gb")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Start Date", type date}, {"End Date", type date}})
in
    #"Changed Type"

 

 

Added two helper columns identifying the earliest start date and latest end date per store.

 

MinDate = CALCULATE(min('Table'[Start Date]),ALLEXCEPT('Table','Table'[Store code]))
MaxDate = CALCULATE(max('Table'[End Date]),ALLEXCEPT('Table','Table'[Store code]))

 

Created calendars for each interval

 

Gaps = 
var s = [Store code]
var a = SELECTCOLUMNS(Filter(ALL('Table'),'Table'[Store code]=s),"Interval",COUNTROWS(CALENDAR('Table'[Start Date],'Table'[End Date])))
return CONCATENATEX(a,[Interval],"|")

 

(COUNTROWS and Concatenatex is for testing)

 

Next step would be to UNION all the calendars and then "subtract"  them from the Min/Max calendar via EXCEPT. That would leave you with all dates that are not covered by bills.

Then the next step would be to group these gaps into a more user friendly output.

 

But - I don't know how to UNION all table values in a table column. The required DAX function does not exist (as far as I know).

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for the solution. I am Okay to make changes in the power query editor as well. But the columns I mentioned are comming from summarised calculated table from other tables. So that calculated table is not visible to make any changes in Query editor. If so, then please suggest the other solution as well. However, I'll try this DAX you have suggested above and let you know of the outcome.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    My DAX is not a solution. I demonstrated where I got stuck when trying to achieve a DAX based solution.

     

    If the columns come from a calculated table they can also come from Power Query. There's not a lot in DAX that Power Query couldn't do (except for the measure thing 🙂 )

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Sure. If so, could you please suggest the solution in Power query. I can try that as well and see if it works. 

      Thanks for you help 

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        5 years ago

        Here is the solution in Power Query.  For each store code it lists all the days that are not covered by any billing period.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BDYAwDAN3ybuSnYTSdJaq+68BFRAEz/Pdw2OIShE1sMJoXLCB+wWz3L6CLX0D4+cjpxMc7K+PFVBBfwJnwifIB9YT5jwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store code" = _t, #"Bill Start Date" = _t, #"Bill End date" = _t]),
    #"Locale Start Date" = Table.AddColumn(Source, "Start Date", each Date.FromText([Bill Start Date],"en-gb")),
    #"Locale End Date" = Table.AddColumn(#"Locale Start Date", "End Date", each Date.FromText([Bill End date],"en-gb")),
    #"Changed Type" = Table.TransformColumnTypes(#"Locale End Date",{{"Start Date", type date}, {"End Date", type date}}),
    #"Current Interval" = Table.AddColumn(#"Changed Type", "Interval", each List.Dates([Start Date],Duration.Days([End Date]-[Start Date])+1,#duration(1,0,0,0))),
    #"All Store Bills" = Table.AddColumn(#"Current Interval", "Store Bills", each Table.SelectRows(#"Current Interval",(k)=>k[Store code]=[Store code])),
    #"Min Date" = Table.AddColumn(#"All Store Bills", "Min Date", each List.Min([Store Bills][Start Date])),
    #"Max Date" = Table.AddColumn(#"Min Date", "Max Date", each List.Max([Store Bills][End Date])),
    #"Grouped Rows" = Table.Group(#"Max Date", {"Store code", "Min Date","Max Date"}, {"Combined", each List.Combine([Interval])}),
    #"Full Period" = Table.AddColumn(#"Grouped Rows", "Full Calendar", each List.Dates([Min Date],Duration.Days([Max Date]-[Min Date])+1,#duration(1,0,0,0))),
    Gaps = Table.AddColumn(#"Full Period", "Difference", each List.Difference([Full Calendar],[Combined])),
    #"Removed Other Columns" = Table.SelectColumns(Gaps,{"Store code", "Difference"}),
    #"Expanded Difference" = Table.ExpandListColumn(#"Removed Other Columns", "Difference")
in
    #"Expanded Difference"