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/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

  • 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

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