Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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          |...
Need Help
Show and Tell
Tips and Tricks
  • sanimesa's avatar
    sanimesa
    8 years ago

    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. 

sanimesa's avatar
sanimesa
Post Prodigy
8 years ago

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.