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() ) )
aduguid
2 years agoMemorable 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()
)
)