cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DLamarche Helper I
Helper I

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 sum for each day that month. Sounds so simple. In Excel I'll do that in one minute (or less). I've been looking for over 2 hours all over YouTube and no one have a simple tip to make it work (but lots of demos though).

I have a Sales table and a Return table. I easily manage to create a Measure Net:=SUM(Sales - Returns) ... keeping it simple here.

I do have a valid Date table BTW.

I tried so many things that I am a bit tired... However, I'm a fighter and I love playing with that stuff.

I tried the expression: MTD:=TOTALMTD(SUM(invoice[Total Sale]),'Calendar'[Date]) to no avail. I saw a few places where they use CALCULATE but the tutorial does not say a thing about CALCULATE.

Below is the result I'm after.

 

Jan

SalesMTD
1/1$118,635$118,635
2/1$132,796$251,431
3/1$140,501$391,932
4/1$130,113$522,045
5/1$158,950$680,995
6/1$133,316$814,311
7/1$156,321$970,632
8/1$118,691$1,089,323

 

I would have prefer to use the Net Sales (Sales - Return) but I can tackle this after Phase I works

I may completely be in the left field but I was hoping that the MTD column would show a Cummulative. I am discouraged coz I always thought that I can grasp things rather quickly!!  8-(

I appreciate a tip.

 

Tks,

Daniel

1 ACCEPTED SOLUTION

Accepted Solutions
DLamarche Helper I
Helper I

Re: About TOTALMTD

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. 🙂

 

View solution in original post

7 REPLIES 7
rajulshah Solution Sage
Solution Sage

Re: About TOTALMTD

Hello @DLamarche ,

 

Well, your formula works as you want it to work. I am unable to understand the issue.
Please see below formula that I tried with sample data.
mtd.png

Please explain your issue in detail. 

DLamarche Helper I
Helper I

Re: About TOTALMTD

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. 🙂

 

View solution in original post

DLamarche Helper I
Helper I

Re: About TOTALMTD

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 Solution Sage
Solution Sage

Re: About TOTALMTD

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 Helper I
Helper I

Re: About TOTALMTD

 

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

 

Sales 2019.jpg2019 Sales Note the total for MTDSales 2020.jpg2020 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 Solution Sage
Solution Sage

Re: About TOTALMTD

Hello @DLamarche ,

 

You can use the following measure:

Measure = SUMX(SUMMARIZE('Table','Table'[Date].[Month]),[MTD])
 
Please see below image:
ytd.png
 
Hope this helps.
DLamarche Helper I
Helper I

Re: About TOTALMTD

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:

Sales 2020 Not Working.jpg

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

Thanks again for your kind help.

Daniel

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

Top Solution Authors
Top Kudoed Authors