cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bw70316 Member
Member

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

Accepted Solutions
bw70316 Member
Member

Re: Calculated Table for Month over Month Changes

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)
4 REPLIES 4
Super User
Super User

Re: Calculated Table for Month over Month Changes

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Highlighted
bw70316 Member
Member

Re: Calculated Table for Month over Month Changes

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

Community Support Team
Community Support Team

Re: Calculated Table for Month over Month Changes

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

bw70316 Member
Member

Re: Calculated Table for Month over Month Changes

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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 392 members 4,503 guests
Please welcome our newest community members: