I'm new to Dax and I'm struggling to get the results I require with a running total. I hope I've give you enough information below to help..
thank for any help and pointer in advance.
I'm trying to create an insurance triangle, I have 3 tables fact_transaction_claims_payments, dimension_development_Periods and reporting_upto_information
fact_transaction_claims_payments
I've managed to get my running total to work but only where there is a development period held in the fact table and not all the ones in between held in my development period dimension
for example i'd expect development period 1 - 10 to total 0.00 and 13 to 18 to total 103,710 but i can seem to get these to appear in my pivot.
Running Total Dax Expression
RT_TotalAmount1:=VAR CurrentDevelopmentPeriod =
CALCULATE(
DATEDIFF(
IF(
MAX('fact_transaction_claims_payments'[cUWYear]) < 2011
, DATE(MAX('fact_transaction_claims_payments'[cUWYear]),1,1)
, DATE(MAX('fact_transaction_claims_payments'[cUWYear]),4,1)
)
,MAX('fact_transaction_claims_payments'[EndOfMonthDate])
,MONTH)
, 'dimension_development_Periods' ) +1
VAR MaxDevelopmentPeriod =
CALCULATE(
DATEDIFF(
IF(
MAX('fact_transaction_claims_payments'[cUWYear]) < 2011
, DATE(MAX('fact_transaction_claims_payments'[cUWYear]),1,1)
, DATE(MAX('fact_transaction_claims_payments'[cUWYear]),4,1)
)
,MAX('reporting_upto_information'[EndOfMonthDate])
,MONTH)
, 'dimension_development_Periods' ) +1
VAR RunningTotal =
CALCULATE (
SUM('fact_transaction_claims_payments'[TotalAmount])
,FILTER (
ALL ('fact_transaction_claims_payments')
,'fact_transaction_claims_payments'[DevelopmentMonthNumber] <= CurrentDevelopmentPeriod
)
)
RETURN
RunningTotal
I've tried adding the ALL('dimension_development_Periods') into the VAR DevelopmentPeriod but this just puts the grand total against every development period.
I'm thinking I now need to use the RunningTotal to calculate against the Development Period Dimension filtered <= the max development period for the cUWYear but I'm not sure on how to implement this and I need some advice can anyone help.