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 ,

 

Can you share some sample data and the expected result to have a clear understanding of your question?

The information you have provided is not making the problem clear to me.

Can you please explain with an example?

 

Best Regards,

Yuna

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Object: Measure that calculates the duration of a claim given a selected date in a slicer
If Claim finalised then Duration = Date Finalised or Date Selected if before Date Finalised minus Date Added
If Claim is not finalised then Duration = Date Selected minus Date Added

If no date selected, then current date should be used rather than Date Selected

 

Data set:

I have a DIM_Time table as my Calendar/Date Table which is the table to be used for Date Selected - "DIM_FullDate".
I have a DIM_Claims table which contains the claim details such as DateFinalised and DateAdded of the claim.
Where the Claim has not been finalised, the DateFinalised in DIM_Claims has been assigned a date in the far future...3000 eve, so I don't want that date to be used, but rather the Current Date or the Date Selected

 

I have started a measure that looks like this, but it is giving syntax errors. Not sure if it is because it is calculating over two tables..DIM_Claims and DIM_Time..