Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Target doesn't update based on period I select

I have 3 tables:

1) equipment

2) targets

3) company

4) calendar

   

Each equipment belongs to specific company and has a relative size.

Each size has a fixed monthly target.

Each company has monthly sales between Jan and Mar '22.

 

In reports view, I want to add data slicer and filter it --> i.e. Last 1 month, last 2 months or last 3 months 

Whie changing the period, I want to visualize if company's sales reach its cumulative target (based on equipment size and q-ty it has) 

For example, company A has total 230$ sales in the last 3 months and cumulative 3 month target of 240$ (as it owns 1 medium and 2 big equipments). Means it didn't achieve the target.

I don't know how to propery connect these tables as my targets don't apply to specific date but are fixed on a monthly basis 😞

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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