topic Re: Finding Gaps Between the dates in DAX Commands and Tips

Finding Gaps Between the dates

mayurbajaj — Thu, 23 May 2024 05:32:06 GMT

Data Table

SiteMeterIdentifier	Account Number	Invoice Start Date	Invoice End Date
1	2	01/11/2023	30/11/2023
1	2	01/01/2024	31/01/2024
1	2	01/03/2024	31/03/2024
1	2	01/04/2024	30/04/2024

Expected Output

SiteMeterIdentifier	Account Number	Invoice Start Date	Invoice End Date
1	2	01/04/2024	30/04/2024
1	2	01/02/2024	29/02/2024

Can this be managed through a DAX Measure?

Re: Finding Gaps Between the dates

mayurbajaj — Thu, 23 May 2024 05:34:01 GMT

Expected Output

SiteMeterIdentifier	Account Number	Invoice Start Date	Invoice End Date
1	2	01/12/2023	31/12/2023
1	2	01/02/2024	29/02/2024

Re: Finding Gaps Between the dates

aduguid — Fri, 24 May 2024 05:15:51 GMT

Create the following calculated columns

Next Invoice Start Date = CALCULATE( MIN(Invoice[Invoice Start Date]), FILTER( Invoice, Invoice[Account Number] = EARLIER(Invoice[Account Number]) && Invoice[SiteMeterIdentifier] = EARLIER(Invoice[SiteMeterIdentifier]) && Invoice[Invoice Start Date] > EARLIER(Invoice[Invoice Start Date]) ) )

Gap Start Date = IF( ISBLANK(Invoice[Next Invoice Start Date]), BLANK(), IF( DATEDIFF(Invoice[Invoice End Date], Invoice[Next Invoice Start Date], DAY) > 1, Invoice[Invoice End Date] + 1, BLANK() ) )

Gap End Date = IF( ISBLANK(Invoice[Next Invoice Start Date]), BLANK(), IF( DATEDIFF(Invoice[Invoice End Date], Invoice[Next Invoice Start Date], DAY) > 1, Invoice[Next Invoice Start Date] - 1, BLANK() ) )