Forum Discussion
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 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
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.
6 Replies
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.
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 Addedjust 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
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 AddedIf 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 SelectedI 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..