Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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. 🙂
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.
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
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?
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
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
Hello @DLamarche ,
You can use the following measure:
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
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. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |