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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AidLow
New Member

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
v-jiascu-msft
Employee
Employee

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.
Ashish_Mathur
Super User
Super User

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/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.