Forum Discussion

orlandopowerbi's avatar
orlandopowerbi
Frequent Visitor
3 years ago

CycleTime : Average Cumulative Duration

Hello everyone, I need a little help with this one that seems very simple but cant seem to write the correct DAX for it.

 

Context

 

I have a sample table of insurance claims and the dates when they were assigned to adjusters, and the duration in days.

 

ClaimIDAssignedDateUnAssignedDateDuration
110/31/202211/30/202230
11/1/20231/4/20233
210/29/202212/28/202260
212/28/20221/6/20239

 

I'm looking for a measure (CycleTime) that calculates the cumulative sum of the Duration for each ClaimID, and then take an average, all this based on the UnAssignedDate.

 

Desired Output

This is the desired output and how it should be calculated:

 

  • November 2022

We only have one unassigned claim (1) so the CycleTime equals that single duration  (30)

 

  • December 2022

Again, we only have one unassigned claim (2) so the CycleTime equals that single duration  (60)

 

  • January 2023

For this month, both claims were unassigned so we calculate the overall duration for each claim and then take an average.

 

Claim 1 : 30 + 3 = 33

Claim 2 : 60 + 9 = 69

CycleTime = (33 + 69)/2 = 51

 

Of course the measure should work for multiple unassignments per claim. 

I attach the csv and pbix sample. Any help would be very appreciated. Thank you very much!