About TOTALMTD

[DLamarche]

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	Sales	MTD
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

[rajulshah]

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.

Please explain your issue in detail. 

[DLamarche]

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  

January	119,107	$119,107
February	133,046	$133,046
March	141,251	$141,251
April	130,763	$130,763

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

November	143,528	$143,528
December	130,417	$130,417
2019 Total	1,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]

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]

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]

Hello @DLamarche ,

You can use the following measure:

Measure = SUMX(SUMMARIZE('Table','Table'[Date].[Month]),[MTD])

Please see below image:

Hope this helps.

[DLamarche]

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

[DLamarche · Accepted solution]

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