Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ccsrtw
Helper I
Helper I

comparing past month sales to this month

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!

 

1 ACCEPTED 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

View solution in original post

8 REPLIES 8
GilbertQ
Super User
Super User

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.

 

https://gqbi.wordpress.com/2016/09/07/create-dynamic-periods-for-fiscal-or-calendar-dates-in-power-b...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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:

 

mom sales comparison.png

 

using the following:

 

Screenshot_1.png

 

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:

 

are cahrt v2.png

 

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.

Hi ccsrtw, Do you need any further assistance on this topic? Regards

I don't have the actual DAX on my now. But it would appear that in your running or Cumulative total you need to have the min calculation. The min date would be aligned to the start of the month.

Another option is depending on how you slice your data to slice the current month and with your calculation it will show the previous month starting from the previous months start date




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.