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 ,

 

Sorry for that it's still not very clear. Can you share some sample data and the expected result to have a clear understanding of your question?

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

 

Best Regards,

Yuna

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

Unfortunately I can share my data set.

Anything else I can provide you with to help me?