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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bw70316
Helper V
Helper V

Calculated Table for Month over Month Changes

I have a table of this:

July: $1,959,814

August: $3,925,980

September: $5,855,582

October: $7,910,275

November: $9,155,231

TOTAL:$28,806,885

 

It is taking the previous month total and adding on the current month's expenses. How do I get a table that does the following:

July: $1,959,814
August= August Total-July= NUMBER I WANT
Sept Total = (August +July) - Sept = NUMBER I WANT
ETC.

 

The total at the end should be 9,155,231 and each month should be 1.8 to 2.1M based on the subtraction of each month.

 

Basically, how do I get the difference from month to to month without having to create a SUM('Expenses'[Expensed]),'Expenses'[Month] = "July" and subtracting the difference for each month??

1 ACCEPTED SOLUTION

This is likely a workable solution, unfortunately I didn't realize you needed my complete data set as these monthly expenditures are the result of a combination of several expenditure items. When I tried this equation, the column that matched was included several times for the different items. trial.png

 

I was able to achieve my desired result with the following DAX:


TotalRev2 =
VAR CumRev =
CALCULATE([Sum Rev Rec.], FILTER(ALL(Revenues), Revenues[MonthNUM]=MAX(Revenues[MonthNUM])-1))
VAR CurRev= [Sum Rev Rec.]

RETURN


(CurRev-CumRev)

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Would depend on your data and your calculations. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thanks for responding! I would simply like the difference or the month's total sales by month, and not a cummulative figure. For example, I am currently getting the following output:

Revenues ReceivedMONTH
$9,061,289.6611
$8,924,656.0710
$7,099,541.629
$5,301,671.968
$3,804,197.987


What I would Like is:


Revenues ReceivedMONTH
$136,63311
$1,825,114.4510
$1,797,8709
$1,497,4748
$3,804,1977


The ACTUAL Sales by Month and not a cummlative total

Hi bw70316,

 

To achieve your requirement, you can create a calculate column using DAX formula below:

Column = 
VAR Previous_Month_Revenues = CALCULATE(MAX(Table1[Revenues Received]), FILTER(Table1, Table1[MONTH] = EARLIEST(Table1[MONTH]) - 1))
RETURN
Table1[Revenues Received] - Previous_Month_Revenues

Capture.PNG 

 

Regards,

Jimmy Tao

This is likely a workable solution, unfortunately I didn't realize you needed my complete data set as these monthly expenditures are the result of a combination of several expenditure items. When I tried this equation, the column that matched was included several times for the different items. trial.png

 

I was able to achieve my desired result with the following DAX:


TotalRev2 =
VAR CumRev =
CALCULATE([Sum Rev Rec.], FILTER(ALL(Revenues), Revenues[MonthNUM]=MAX(Revenues[MonthNUM])-1))
VAR CurRev= [Sum Rev Rec.]

RETURN


(CurRev-CumRev)

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors