How to find the rows with missing date range

[Anonymous]

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 code	Bill Start Date	Bill End date
1	12/05/2020	14/06/2020
1	15/07/2020	17/08/2020
1	18/08/2020	13/09/2020
81	01/03/2020	30/03/2020
81	01/05/2020	29/05/2020

Output:

Store code	Missing Start Date	Missing End date
1	15/06/2020	14/07/2020
81	01/04/2020	30/04/2020

[lbendlin]

@WillT This is where a UNIONX function would come in really handy...

[lbendlin]

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

[Anonymous]

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

Thanks Ibendlin.

[lbendlin]

@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]

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]

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]

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]

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"

[Anonymous]

Hi lbendlin,

I am not sure how to add these transformation steps to a calculated table. I am new user of Power BI. Could you please let me know. I think these steps can only be added in Query editor.

[lbendlin]

Yes, in Power Query add a blank source, then open its Advanced Editor and replace the code there with what I posted.

[Anonymous]

The data set I posted on this query is not a real one. There are other columns with Start date, end date all coming from summarised calculated table in PBI desktop. So , I need to perform operation on this calculated table. Is there any ways, I can get this calculated table from desktop to Query editor where I can perform those actions.

Or in the code you have attached, is it possible to change the data source to calculated table ? 

[lbendlin]

I don't know how to do it in DAX without a UNIONX function.