cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AidLow Visitor
Visitor

Compare Rolling Total for this Month With Last Month's Revenue over same Period

Hello everyone!

 

Complete newcomer to Power BI here. I've only been introduced to it this week but I'm already really enjoying the customization and functionality that the software offers. Since I'm not super familiar with DAX or anything like that, I've made extensive use of some of the resources available on here, but I'm still having one problem that's persisting.

 

In my dataset I have a heavy amount of transaction data (several hundred thousand rows, at least), and I'm trying to find a way for us to compare our current revenue in June (which is incomplete) and compare it to an identical range in the previous month. I've already created a date table (date) in addition to the transaction data table (Query1).

 

Essentially, I want to compare June's MTD sales with what May's MTD sales would have been at the same time. So, for example, I could be comparing sales from June 1st to June 15, and I'd want to see how those numbers compare with ours from May 1 - May 15.

 

I've managed to write a function that let's me view current June numbers against all of May's, but I still am unable to compare the specified date ranges easily. I can manually do it in the filters, but I'm hoping there's an easier way to do it.

 

I've also tried running the in several different ways, and in every case I'm able to get May's total revenue, but not it's running total to compare with June's. Below are what I managed to put together for June's rolling total and May's total. The scripts were originally used to compare the last full month and the month prior to that one (Like May and April, for instance).

 

ThisMonth = CALCULATE(SUM(Query1[Amount]), FILTER(Query1, IF(OR(MONTH(TODAY())=11,month(TODAY())=12), Query1[Year]=YEAR(TODAY()), Query1[Year]=YEAR(TODAY()))), FILTER(Query1, Query1[Month]=MONTH(TODAY())))

For Last Month:

 

LastMonth = CALCULATE(SUM(Query1[Amount]), FILTER(Query1, IF(OR(MONTH(TODAY())-1=11,month(TODAY())-1=12),Query1[Year]=YEAR(TODAY())-1, Query1[Year]=YEAR(TODAY()))), FILTER(Query1, Query1[Month]=MONTH(TODAY())-1))

I've tried messing around with the DATEADD and PARALLELPERIOD functions, as well, but haven't been able to gain much traction with them. I feel like potentially incorporating a DATEADD component into the second of these two scripts could work, but I'm not sure.

 

Finally, I tweaked a script that I found that said it would give me the previous month's rolling total, but I ended up getting the same issue as above, where I got the full amount for May and not for a range that matches the amount of days that have elapsed in June. I've attached that script as well.

 

Previous Month Rolling Total = CALCULATE(SUM(Query1[Amount]), FILTER(ALL('Date'),('Date'[Monthnumber])=MAX('Date'[Monthnumber])-1 && 'Date'[Day] <=MAX('Date'[Day])))

Let me know if I can clarify anything. Again, these scripts all run well, I'm just too inexperienced (I think) to figure out how to tweak them right now.

2 REPLIES 2
Super User IV
Super User IV

Re: Compare Rolling Total for this Month With Last Month's Revenue over same Period

Hi,

 

Create a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  In the slicer/Filter section, drag the Date from the Calendar Table and select any single date there.  Enter these measures

 

[MTD revenue]=CALCULATE(SUM(Data[Amount]),DATESMTD(Calendar[Date]))

 

[MTD revenue in previous month]=CALCULATE([MTD revenue],DATESBETWEEN(Calendar[Date],EOMONTH(EDATE(MIN(Calendar[Date]),-1),-1)+1,EDATE(MIN(Calendar[Date]),-1)))

 

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Microsoft v-jiascu-msft
Microsoft

Re: Compare Rolling Total for this Month With Last Month's Revenue over same Period

Hi @AidLow,

 

Did you get the answer?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors