Finding Gaps Between the dates

[mayurbajaj]

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?

[aduguid · Accepted solution]

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]

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