DAX for Period Duration calculation

[Anonymous]

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 claim is not finalised, then it should calculate the days from the date added to the date selected via a slicer. (I am not sure what to do about the scenario where a date is not selected and the claim is not yet finalised..)

This is my attempt and it has some problems.

re the filtering of "3000". the model has a date in year 3000 when the claim does not have a date finalised..

@amitchandak 

[Anonymous · Accepted solution]

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]

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

[Anonymous]

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]

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..

[Anonymous]

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]

Hi @Anonymous 

Unfortunately I can share my data set.

Anything else I can provide you with to help me?