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/20...
- 2 years ago
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() ) )
mayurbajaj
2 years agoNew 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 |