Forum Discussion

mayurbajaj's avatar
mayurbajaj
New Member
2 years ago
Solved

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...
  • aduguid's avatar
    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()
        )
    )