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: […]
i have a model to calculate incentive for sales based on target per shift
so i have tables for fingerprint attendance and daily target per store and sales for emp and store
what i need to do is create a measure to distribute store daily target between employees according to predefined shifts
For example i have store
one emp come at 10 am to 5pm , the second one at 12 pm to 8 pm , the third one at 2 pm to 10 pm
and the first shif in store begins at 10 am till 2 pm
The second shift begins at 2 pm till 10 pm
so for the first emp he will get 100% of store target till 12 pm then 50% till 2 pm then 33% till 5 pm and so on
so any help
Hi Ahmed,
What I think you would need to do is to model your data for the different shifts. This will then allow you to get the number of hours per shift which you could then use to do the calculations?
hi Gilbert ,
thank you for your reply
what i need to do
i have store shifts from 9 am to 3 pm(morning shift) and another one from 3 pm to 11 pm (evening shift)
morning shift will be 30% of daily target and evening will be 70% of daily target
i want a measure to distribute daily target between sales staff according to fingerprint time equally in the same shift
for example :
1- if an employee has shown fingerprint at 9 am alone, he will get 100% of morning shift otherwise he will get 50% if another staff shows fingerprint
2- if an employee shows fingerprint at 12 pm , so he will get percentage of morning shift and and percentage of evening shift (overlap)
i have tables for for fingerprint and table for target and sales and shift
Thanks
Hi Ahmed
It sounds to me like you would need to model this data in Power Query, which would make the measure at the end easier?