Forum Discussion
End date day count
- 1 year ago
You could create a calculated column like
Day count = VAR EndDate = 'Table'[End Date] VAR NextStartDate = CALCULATE ( MIN ( 'Table'[Start Date] ), ALLEXCEPT ( 'Table', 'Table'[Location], 'Table'[Kennel] ), 'Table'[Start Date] >= EndDate ) VAR Result = DATEDIFF ( EndDate, COALESCE ( NextStartDate, TODAY () ), DAY ) RETURN Result - 1 year ago
RichOB Create a calculated column to find the next start date for each kennel:
Next Start Date =
VAR CurrentKennel = 'Table'[Kennel]
VAR CurrentEndDate = 'Table'[End Date]
RETURN
CALCULATE(
MIN('Table'[Start Date]),
FILTER(
'Table',
'Table'[Kennel] = CurrentKennel &&
'Table'[Start Date] > CurrentEndDate
)
)Create a calculated column to calculate the day count:
DAX
Day Count =
VAR NextStart = 'Table'[Next Start Date]
VAR CurrentEnd = 'Table'[End Date]
RETURN
IF(
ISBLANK(NextStart),
DATEDIFF(CurrentEnd, TODAY(), DAY),
DATEDIFF(CurrentEnd, NextStart, DAY)
)
RichOB Create a calculated column to find the next start date for each kennel:
Next Start Date =
VAR CurrentKennel = 'Table'[Kennel]
VAR CurrentEndDate = 'Table'[End Date]
RETURN
CALCULATE(
MIN('Table'[Start Date]),
FILTER(
'Table',
'Table'[Kennel] = CurrentKennel &&
'Table'[Start Date] > CurrentEndDate
)
)
Create a calculated column to calculate the day count:
DAX
Day Count =
VAR NextStart = 'Table'[Next Start Date]
VAR CurrentEnd = 'Table'[End Date]
RETURN
IF(
ISBLANK(NextStart),
DATEDIFF(CurrentEnd, TODAY(), DAY),
DATEDIFF(CurrentEnd, NextStart, DAY)
)