Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Calcuations

Hi,

 

I have 2 tables - one with sales advisors and target information set a day level and a month level:

 

AdvisorDays WorkedTotal Work DaysDaily TargetMonthly Target
A Person162190018,900
B Person142141018,900

 

and a scond with sales data in it.

 

I want to calculate a Month to Date Target column based on the number of days each advisor has worked x the daily target. Sounds simple but I ethier get the month to date target for the first advsior for all advisors, or a total of all advisors combined for each.

 

Anybody have any ideas?

 

Thanks in advance

Need Help

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous where is the date column in this table to calculate MTD?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It is in an Excel worksheet - which I have inherited this week.

       

      The days worked is formualted in excel based on a work roster, so this value will increase by one each day.

       

      There is no actual date field within this table.

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous not sure what to tell given the data. Let me ask this, how you will do this in Excel?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      A staightforeward =sum(days worked * daily target) to give the month to date target.

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous same here, add a new calculated column

     

    MTD Target = Table[Days Worked] * Table[Daily Target]

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Tried this already - gives 17 for all advisors in a visual, but works out the correct MTD target in a view of the table

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Figured it out - I changed where I was getting the advisor name from to the sales table and the calculations worked.