Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

MTD sum does not give total till date

I have sales and target colums in sales report. 

 

i wanted to make target month to date.

for sales i used the simple formula:

MTD Sales= TOTALMTD([NetTotal] ,SalesmanTarget[TargetDay])     NetTotal is the sum of sales & TargetDay is the date column
the result is correct 
I made another formula 
MTD Target= TOTALMTD([Actual Target],SalesmanTarget[TargetDay]) 
where 
Actual Target= SUM(SalesmanTarget[TargetAmount])
 
and the results are as follows MTD sales and Actual Target are correct, but the MTD target is not. MTD Target should be the target assigned to the sales team from start of the current month till date. but as you see i am not reaching the correct sum for MTD Target. 

is there a mistake in my formulas?

 

I appreciate your help and support

DAX
MTD

18 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,


    I would try uisng a calendar table for date column reference. Since DAX is filter context dependand I suspect now your measure is calculating day level sales instead of MTD values.


    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your reply. 

       

      I have the date table ready. so now i tried with the date table but the results did not change. 

      as you can see below, the MTD Target and Actual Target are still equal. The formulas are as follows:

      MTD Sales = TOTALMTD([NetTotal] ,DatesTable[Date])
      MTD Target = TOTALMTD([Actual Target] ,DatesTable[Date])

       

       

       

       

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous 

        in your report you are slicing by a column that is not visible in the screenshot. Is that the "month name" column from the date table? If not what is it?

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    UPDATE I also tried the following formula, 

     

    MTD Target = CALCULATE(SUM([Actual Target]), DATESMTD(SalesmanTarget[TargetDay]))
    MTD Target= CALCULATE(SUM([Actual Target]), DATESMTD(DatesTable[Date]))

    Issue is not resolved yet
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    UPDATE 

    I tried the builtin measure and the same issue still not resolved.


    TargetAmount MTD =
    IF(
        ISFILTERED(SalesmanTarget[TargetDay]),
        TOTALMTD(SUM('SalesmanTarget'[TargetAmount]), SalesmanTarget[TargetDay])
    )
    the result is blank,
     

     

    • ValtteriN's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi,

      How do you calculate [NetTotal]? This might be the root problem.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I am using [NetTotal] in calculating sales only. nettotal is sum of sales coming from TransactionEntry table. theres no complicated calculation in NetTotal.

        Target is a bigger issue though. i am trying to sum the target, but i dont need the whole moth target, i need only the Sum of target till date or MTD.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous ,
    Can you please share a acreenshot of your data model showing the realtionships and connected culumns? Also can you confirm what are slicing by? And from which table?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

      thank you for your reply.

       

      Additional step i took today. I removed PowerBI and installed the latest version, restarted my machine. I thought maybe some calculations stuck in memory. 😁 Just for sake of me doing whatever it takes.

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi

        you should be using dates only from date table. Slice by month from the date table only. Time intelligent functions works only with a standard date tables that is marked as date table. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    UPDATE

     

    When I try to creat MTD by Quick Measure, it gives me the following error:

    Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column
     
    Does that mean anything in context of the issue?
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous 

      Please refer to my reply below. You should be slicing by date hierarchy not area. MTD has no meaning if you slice by area!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    UPDATE

     

    When I try to creat MTD by Quick Measure, it gives me the following error:

    Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column
     
    Does that mean anything in context of the issue?
  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    Not sure what your context is like, but you can try my formula:

    MTD = 
CALCULATE(SUM(Table2[Sale]),FILTER(ALL(Table2),EOMONTH('Table2'[Date],0)=EOMONTH(MAX('Table2'[Date]),0)&&'Table2'[Date]<=MAX('Table2'[Date])))

    I use this formula a lot and it always works for me, and this doesn't need to rely on a date table.

     

    Result:

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.