Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to show how October's sales so far are comparing to last month's sales to the day, i.e. 9/1/16 through 9/5/16 compared to 10/1/16 through 10/5/16.
I have a calendar table linked to two separate data sources, one with this month's sales and one with last month's. I thought it would be as simple as creating an area chart, using DayOfMonth as the axis, and using the two revenue fields from the reports as values. I was definitely mistaken and I'm not sure where to go from here - any direction would be much appreciated.
Thanks!
Solved! Go to Solution.
Hi ccsrtw,
If we would like the running total to "reset" to 0 at each beginning of every month, we could change the measure formula with the one below:
Sales Running Total = CALCULATE(SUM('Direct - In-Month CW'[MCV]),
DATESMTD('Direct - In-Month CW'[Close Date])
)
Writing the formula this way would reset the total at the begining of the month, but the total of all dates would replaced by the total of the last month.
The formula you used add the totals from the begining of the date and end up with the last date.
There is also a blog talking about calculate running total, see:
Running Total Techniques in DAX
Please post back if you need any further assistance on this topic.
Regards
Hi there
Below is a blog post where I explain how to easily create your own periods. And once you have created your own periods, you can then very quickly and easily create your own measures.
This would work perfectly in your scenario, because you could create 2 periods:
MTD (Month to Date)
LMTD (Last Month to Date)
Then you can create 2 measures using the periods above which will then overlay each other?
Please let me know if you get stuck or got any questions, but this does work really well.
hey guavaq,
attempting to apply your solution to my problem now - will let you know how it turns out! appreciate your reply.
If you get stuck or got any questions please let me know, happy to help out or explain.
So the problem has evolved somewhat...
I'm looking for the sales totals to cumulate as the month goes on, a running total, not just spike on the associated day. I was able to accomplish this with the following formula:
Sales Running Total = CALCULATE(SUM('Direct - In-Month CW'[MCV]),FILTER(ALL('Direct - In-Month CW'),'Direct - In-Month CW'[Close Date] <= MAX('Direct - In-Month CW'[Close Date])))
This leaves me with the below:
using the following:
So we're almost there, I basically just need to get the rolling total to 'reset' once the new month starts instead of building off of the prior month's total. Does this make sense?
Hi ccsrtw,
If we would like the running total to "reset" to 0 at each beginning of every month, we could change the measure formula with the one below:
Sales Running Total = CALCULATE(SUM('Direct - In-Month CW'[MCV]),
DATESMTD('Direct - In-Month CW'[Close Date])
)
Writing the formula this way would reset the total at the begining of the month, but the total of all dates would replaced by the total of the last month.
The formula you used add the totals from the begining of the date and end up with the last date.
There is also a blog talking about calculate running total, see:
Running Total Techniques in DAX
Please post back if you need any further assistance on this topic.
Regards
Hi Michael,
I plugged in your calculation and got the following in my area chart:
So we lost the running total aspect. I'm currently reading through that blog post to see if I can figure this out on my own by applying some of those techniques, but I appreciate the assistance in the meantime.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |