Using DAX to calculate Cumulative Target amount split into individual months
I had a requirement to take the overall target amount and split it for each month. But also, for it to be cumulative for each month going forward.
The Target amount was 600,000 per month
This is what the DAX measure looked like
Target by Month = VAR MyTarget = 6 * 6000000 VAR TargetPerMonth = DIVIDE ( MyTarget, 6 ) VAR MonthNumber = MIN ( 'Date'[Calendar Month Number] ) VAR CumulativeTarget = CALCULATE ( TargetPerMonth * MonthNumber, FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Order'[Order Date Key] ) ) ) RETURN CumulativeTarget
And this is what it looked like when used with a cumulative total
Sometimes I find that it is easy to create a DAX measure to solve a business problem without having to go back and try and create some data in the source.
I hope you found this useful and as always comments or suggestions are most welcome.