Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX for Period Duration calculation

Hi all   I am trying to create a measure that calculates the duration of the period that a claim is outstanding. i.e. when the claim is added to the DB and when the claim is finalised in DB. If the...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Based on your description, you can create a measure as follows.

    Test table:

    measure:

    Measure = 

    var x1=SELECTEDVALUE('DIM_Time'[DIM_FullDate])

    return

    IF(

     DATEDIFF(SELECTEDVALUE('DIM_Time'[DIM_FullDate]),MAX('DIM_Claims'[Date_finalised]),YEAR)<=900,

     IF(

     x1<MAX('DIM_Claims'[Date_finalised])-MAX('DIM_Claims'[Date_Added]),

     x1,

     MAX('DIM_Claims'[DateFinalised])

     ),

     x1-MAX('DIM_Claims'[Date_Added]))
     
    Result:

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

Based on your description, you can create a measure as follows.

Test table:

measure:

Measure = 

var x1=SELECTEDVALUE('DIM_Time'[DIM_FullDate])

return

IF(

 DATEDIFF(SELECTEDVALUE('DIM_Time'[DIM_FullDate]),MAX('DIM_Claims'[Date_finalised]),YEAR)<=900,

 IF(

 x1<MAX('DIM_Claims'[Date_finalised])-MAX('DIM_Claims'[Date_Added]),

 x1,

 MAX('DIM_Claims'[DateFinalised])

 ),

 x1-MAX('DIM_Claims'[Date_Added]))
 
Result:

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

I dont get it. Please can you share the pbix for this if you still have it?

 

I am expecting a value representing amount of days in the measure result, but your screenshot shows a date..

 

If the datefinalised is 3000 01 01 (being unfinalised) then the days should be dateselected DIM Time Full Date minus DIM Claims Date Added

If the date is finalised (not 3000 01 01) and the date finalised is earlier than date selected then the days should be DIM Claims Date Finalised minus DIM Claims Date Added 
otherwise if the date finalised is (not 3000 01 01) and the date finalised is after the date selected then the days should be DIM Time Date Selected minus Date Added


just a reminder the Date selected DIM Time Full Date is on its own table (DIM Time) and Date Finalised and Date Added is another table DIM Claims.
also, there is only row per claim number and thus I dont have to calculate the latest Date Finalised etc