Forum Discussion
Target doesn't update based on period I select
Hi Anonymous ,
I create a sample to have a test.
Data model:
Period table is an unrelated dax table for slicer.
Period = DATATABLE ( "Period", STRING, "Last Month", INTEGER, { { "Last 1 month", 1 }, { "Last 2 months", 2 }, { "Last 3 months", 3 } } )Measure:
Achieve Target or not = VAR _Last_Month = SELECTEDVALUE(Period[Last Month]) VAR _Sales_IN_Last_Months = CALCULATE(SUM(Company[Sales ($)]),FILTER(Company,Company[Month] >= EOMONTH(MAX(Company[Month]),-_Last_Month)+1)) VAR _ADD = ADDCOLUMNS(Equipment,"Target",RELATED(Targets[Monthly target])) VAR _SUMMARIZE = SUMMARIZE(_ADD,[Company],"Target",SUMX(FILTER( _ADD,[Company] = EARLIER([Company])),[Target])) VAR _Target_IN_Last_Months = SUMX(_ADD,[Target]) * _Last_Month RETURN IF(_Sales_IN_Last_Months >= _Target_IN_Last_Months,"Yes","No")Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I create a sample to have a test.
Data model:
Period table is an unrelated dax table for slicer.
Period =
DATATABLE (
"Period", STRING,
"Last Month", INTEGER,
{
{ "Last 1 month", 1 },
{ "Last 2 months", 2 },
{ "Last 3 months", 3 }
}
)Measure:
Achieve Target or not =
VAR _Last_Month = SELECTEDVALUE(Period[Last Month])
VAR _Sales_IN_Last_Months = CALCULATE(SUM(Company[Sales ($)]),FILTER(Company,Company[Month] >= EOMONTH(MAX(Company[Month]),-_Last_Month)+1))
VAR _ADD = ADDCOLUMNS(Equipment,"Target",RELATED(Targets[Monthly target]))
VAR _SUMMARIZE = SUMMARIZE(_ADD,[Company],"Target",SUMX(FILTER( _ADD,[Company] = EARLIER([Company])),[Target]))
VAR _Target_IN_Last_Months = SUMX(_ADD,[Target]) * _Last_Month
RETURN
IF(_Sales_IN_Last_Months >= _Target_IN_Last_Months,"Yes","No")Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thank you so much for the solution ! It's exactly what I needed 🙂
Could you please also help me to calculate the following measure?
I need to calculate which equipment achieved its cumulative target based on sales of company it belongs to.
For example, in the last 1 month company B has 75$ sales. It has 3 equipments. Cumulative target of Equipment 1 is 10, Equipment 4 is 30 (10+20), Equipment 8 is 60 (10+20+30).
In the last 1 month, equipment 1 achieved its cumulative target (10 vs 75), equipment 4 achieve (30 vs 75), equipment 8 achieved (60 vs 75).
In another example, in the last 1 month company A has 80$ sales. It has 3 equipments. Cumulative target of Equipment 2 is 20, Equipment 3 is 50 (20+30), Equipment 10 is 80 (20+30+30). In the last 1 month, all three has achieved their cumulative targets (20 vs 80, 50 vs 80, 80 vs 80).
For cumulative target logic it needs to check if equipments belong to the same company, always starts with smallest equipment fixed target and equipment # and adds up as targets and # increases. So if company X has 3 small equipments, it will add app from smallest to largest #
Equipment 1 = 10
Equipment 2 = 20 (10+10)
Equipment 3 = 30 (10+10+10)
Thank you once again!
I've tried using calculated columns:
Target = RELATED(Target[Monthly target])Cumulative target = CALCULATE(SUM(Equipment[Target]), FILTER(Equipment, Equipment[Company] = earlier(Equipment[Company])), FILTER(Equipment, Equipment[Target] <= earlier (Equipment[Target])), FILTER(Equipment, Equipment[Equipment ID] <= earlier(Equipment[Equipment ID])))It works for company A , but for company C it calculates incorrectly (equipment 9 should be 20 because it's smaller target, equipment 5 should be 50.I don't know how to make it calculate properly using measure as well