Forum Discussion
mayurbajaj
2 years agoNew Member
Finding Gaps Between the dates
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?
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() ) )
2 Replies
- mayurbajajNew Member
Expected Output
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
- aduguidMemorable Member
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() ) )