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 current table has data as below. With non- uniform date ranges for each store. I need to find missing invoices with date range for each Store. The output may look like below. I really appreciate your help.

Thanks,

Sush

 

Present data condition: 

Store codeBill Start DateBill End date
112/05/202014/06/2020
115/07/202017/08/2020
118/08/202013/09/2020
8101/03/202030/03/2020
8101/05/202029/05/2020

 

Output:

Store codeMissing Start DateMissing End date
115/06/202014/07/2020
8101/04/202030/04/2020

 

12 Replies

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

    Anonymous Would you like this in Power Query or in DAX?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      DAX if possible please. Since those columns are coming from calculated table. 

      Thanks Ibendlin.

      • 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).