Thanks in advance for your valuable time and helping me solve this!

I am trying to create Cumulative failure rate which is calculated as running total failures divide by running total claims.

I have a vehicle details table and claims table. Vehicle table is connected using key vin with 1 to many relationship with claims table.

I want to plot table CFR by months passed from earliest date based on legend used.

I know that if I want to calculate dynamically value of earliest date than calculated column is not a good choice but calculating measure is not an option either as I cannot use as x axis on line chart. Please suggest me an alternative option.

month passed = Claim Date – Earliest date in vehicle details table based on legend chosen
running claims = count of claims before selected value of month passed
running sales = count of distinct vins where warrantystart date is not null before selected value of month passed

I have created temporary work around as follow:

Created Sales Start Date table by carline and merge it with warranty table on carline column

Created this calculated column
‘Warranty Claim Parts'[Elapsed_Month] Which now calculates date diff between claim date and sales start date

Sales measure:

Sales = calculate(DISTINCTCOUNT(VehicleDetails[VIN]), NOT ISBLANK(VehicleDetails[WarrantyStartDate]))

RunningTotalClaims Measure:

RunningTotalClaims =
var x= max(‘Warranty Claim Parts'[Elapsed_Month])

RETURN
CALCULATE(
DISTINCTCOUNT(‘Warranty Claim Parts'[MainClaim#]),
‘Warranty Claim Parts'[Elapsed_Month] <= x
)

RunningTotalSalesmeasure:

Created a sales table and create many to many relationship with vehicle details table

Note that legend I would use is from vehicle details table and also can filter claims table.

RunningTotalSales =
VAR CurrentClaimMonth = max(‘Warranty Claim Parts'[ClaimdateMonth])
RETURN
CALCULATE(
SUM(Sales[sales]),
FILTER(
Sales,
Sales[SalesMonth] <= CurrentClaimMonth
)
)

CFR% measure is:

CFR%= [RunningTotalClaims]/[RunningTotalSales]

Sales[SalesMonth] & ‘Warranty Claim Parts'[ClaimdateMonth] both are in same format YYYYMM

enter image description here

Note that in this one I gave hard dates in warranty claims table by merging sales start date table which restrict me from using other legends or say another upper hierarchy level legend such as model code etc…

In addition, with this work around I can publish report on web version but cannot perform schedule refresh which gives me data type error without telling specific column in claims table. I have to refresh locally and then publish it to web version which is really slow around 1-2 hours.

This above stated error is because of above complex calculation because when I publish report without CFR visual and leave these measures in dataset error does not persist. I have also checked data type of each column which seems fine.

Please suggest me a solution.