Forum Discussion
date difference open and closed date
It might be the friday syndrome, but i cannot seem to see how to calclulate the date difference between to rows
i have the following
Case number | Type | Date
123456 | Case closed | 29-08-2017
123456 | Case created | 09-12-2016
The result should be 263 days to process this case
hope someone can help
thanks in advance
A measure like this will work in some circumstances - if you add case numbers and this measure in a table:
Days to Process =
VAR date_created = CALCULATE(MAX('Cases'[Date]), 'Cases'[Type]="Case created")
VAR date_closed = CALCULATE(MAX('Cases'[Date]), 'Cases'[Type]="Case closed")
RETURN DATEDIFF(date_created, date_closed,DAY)
//RETURN CALCULATE(COUNTROWS(DimDate), AND(DimDate[Date]>date_created, DimDate[Date]<date_closed), NOT(WEEKDAY(DimDate[Date]) in {1, 7}))But the difference between those two dates is 351, the commented line is to exclude weekends, even that yields 250.
2 Replies
A measure like this will work in some circumstances - if you add case numbers and this measure in a table:
Days to Process =
VAR date_created = CALCULATE(MAX('Cases'[Date]), 'Cases'[Type]="Case created")
VAR date_closed = CALCULATE(MAX('Cases'[Date]), 'Cases'[Type]="Case closed")
RETURN DATEDIFF(date_created, date_closed,DAY)
//RETURN CALCULATE(COUNTROWS(DimDate), AND(DimDate[Date]>date_created, DimDate[Date]<date_closed), NOT(WEEKDAY(DimDate[Date]) in {1, 7}))But the difference between those two dates is 351, the commented line is to exclude weekends, even that yields 250.