Forum Discussion
How to find the rows with missing date range
Anonymous Would you like this in Power Query or in DAX?
DAX if possible please. Since those columns are coming from calculated table.
Thanks Ibendlin.
- lbendlin5 years ago
Super User
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).
- Anonymous5 years agoNot applicable
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.
- lbendlin5 years ago
Super User
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 🙂 )