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
Summary
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.
Hi, thank you for your post.
2 questions:
1. Why in the VAR definition you multiply the monthly target by 6 and then divide it by 6?
2. It seems the total target of the full year at the end of the table is not correct, it should be 72m.
Hi Roberto,
Thanks for the questions and reading my blog post.
1. You are correct this was something I did but looking back it makes no sense.
2. Once again in my blog post it should be for 6 months and not the 12 months shown. Which will then reflect the right total.
Great post Gilbert. Learning from it and playing around with DAX I think we could make a couple of improvements.
Target by Month = VAR vMyTarget = 6000000
VAR vMinMonthNumber = MIN ( DimDate[MonthNumberOfYear] )
VAR vMaxMonthNumber =
MAX ( DimDate[MonthNumberOfYear] ) //To calculate the Cumulative Target for the Grand Total.
VAR vCumulativeTarget =
CALCULATE (
vMyTarget * vMinMonthNumber,
FILTER ( ALLSELECTED ( DimDate[Date] ), DimDate[Date] 0, vCumulativeTarget ), //In case you have Year-Months without sales.
vMyTarget * vMaxMonthNumber //To get on the Grand Total row the Cumulative Target of the latest Year-Month shown on the pivot table.
)
Hi there
Thanks for the DAX it looks really good I will need to give it a whirl on my dataset. Thanks for the idea!
Gilbert, I don’t know why when I sent the reply message with the DAX measure code, there are missing parts. Because I don’t know how to email you or DM you on twitter I put my reply with the correct DAX code on your twitter message.
[…] Gilbert Quevauvilliers uses DAX to calculate a straight-line target: […]