CycleTime : Average Cumulative Duration

[orlandopowerbi]

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.

ClaimID	AssignedDate	UnAssignedDate	Duration
1	10/31/2022	11/30/2022	30
1	1/1/2023	1/4/2023	3
2	10/29/2022	12/28/2022	60
2	12/28/2022	1/6/2023	9

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!

[amitchandak]

@orlandopowerbi , Check if you need something like this

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA