Forum Discussion

DLamarche's avatar
DLamarche
Icon for Helper I rankHelper I
6 years ago
Solved

About TOTALMTD

Hi All! I have registered to Udemy for a DAX intro course (and the course is really bad!). In one exercise I need to use TOTALMTD to show the sales for all month in a year and next to it the running...
Tips and Tricks
Tutorial Requests
  • DLamarche's avatar
    DLamarche
    6 years ago

    Hello Rajulshah,

    To be honest I was trying this in Excel PowerPivot but my understanding would be that DAX is DAX in BI and in PP in Excel data model. When I do the Pivot Table the second column is not a cummulative if the transation that month.

    Okay, I'll try one more time and I will be extremely careful to use that formula (except for the field names). and get back to you.

    Thanks for getting back to me Rajulshah. 🙂

     

DLamarche's avatar
DLamarche
Icon for Helper I rankHelper I
6 years ago

Hello Rajulshah,

 

Thank you for your help, I redid it very carefully. Now it works. Thank you very much.

I used the formula: MTD:=TOTALMTD(Sum([Total Sale]),'Calendar'[Date]) I think I used a faulty measure instead of "Total Sales".

 

Now can I ask you a small favour? Now what is happening is that the total for each month is the same in both column.

2019  

January119,107$119,107
February133,046$133,046
March141,251$141,251
April130,763$130,763

 

And the total at the end of the year is wrong in the MTD column:

November143,528     $143,528     
December130,417$130,417
2019 Total1,613,049$130,417

 

I understand that the MTD is fine but I should use TOTALYTD (I think) for the yearly total right?

Am I on the right track? I am very new to this but I love it alot!

 

Oops I will try to go back to the previous post and mark it a "Great" for you. I didn't know that posting to your answer would create another post!!

Daniel

rajulshah's avatar
rajulshah
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hello DLamarche ,

 

So do you want cumulative total by months? Can you provide an image that shows the month, it's sales and the value of the measure?

  • DLamarche's avatar
    DLamarche
    Icon for Helper I rankHelper I
    6 years ago
     

    Thanks again Rajulshah for your help for the MTD.

    If you look at the 2019 & 2010 Sales Total is is obviously correct.

    However, for the 2019 & 2020 MTD Total is it obviously not correct.

    The MTD total for 2019 should be 1,613,049

    The MTD total for 2020 should be 1,596,044

    The MTY Grand Total should be 3,209,093

    Like they do in the Sales column

     

    2019 Sales Note the total for MTD2020 Sales Note the total for MTD

    Thanks again if you can guide in the right direction. I this that YTD need to be involved but I am not sure how to use it in my Model.

    If you cannot that's fine hey! I'll continue my research.

    Daniel

    • rajulshah's avatar
      rajulshah
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

      Hello DLamarche ,

       

      You can use the following measure:

      Measure = SUMX(SUMMARIZE('Table','Table'[Date].[Month]),[MTD])
       
      Please see below image:
       
      Hope this helps.
      • DLamarche's avatar
        DLamarche
        Icon for Helper I rankHelper I
        6 years ago

        Hello again Rajulshah. Sorry for not getting back to you I was taken the whole weekend.

        I tried your expression but it always return an error when evaluated. And even if was successful I am not sure where / how I would use it in my Pivot Table. Presently this is what I have:

        I'd like the Grand Total for the column YTD to be 3,209,093

        Thanks again for your kind help.

        Daniel