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

Difference of Revenue on Month to Month Basis

I have looked into this with LASTMONTH and DATEBETWEEN and have had no luck. Perhaps I am doing something wrong. I have a Master Date table connected to my Revenue Table which includes Revenue Received Dates. I am trying to display the data in a matrix, but naturally I don't want them to add onto one another. Instead, I would like to see the month's total for each row. I feel like I can do this with a Calculate(SUM('Rev'Revenues Rec),'Rev Item'='July' and do the same for August and subtract the two for each month, but that seems ridiculous. Below is an example of my rev rec:



July: $1,959,814

August: $3,925,980
September: $5,855,582
October: $7,910,275

November: $9,155,231


TOTAL:$28,806,885

I would like the total to be the November number of 9,155,231 instead of the addition. Thanks for any help!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-danhe-msft Super Contributor
Super Contributor

Re: Difference of Revenue on Month to Month Basis

Hi @bw70316,

Based on my test, you could refer to below steps:

Sample data:

1.PNG

Create below calculated columns:

Index = RANKX('Table1','Table1'[Value],,ASC,Dense)
Value_ = var a=Table1[Index]-1
return [Value]-CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Index]=a))

Result:

1.PNG

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Difference of Revenue on Month to Month Basis

Seems like there are multiple issues here but I can't exactly say what they are from the information given. However, seems like you could make use of:

 

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

For the difference of revenue on a month-to-month basis

 

For measure totals. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

If none of that helps, please re-state the issue and refer to: 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!

bw70316 Member
Member

Re: Difference of Revenue on Month to Month Basis

Thanks, you certainly have given me a lot to look into. What I am looking for is some like this:

July: $1,959,814
August: $3,925,980
September: $5,855,582$5,855,582
October: $7,910,275
November:$9,155,231


To be something like this:

July: $1,959,814
August: $1,966,166
September: $5,855,582$1,929,602
October: $2,054,693
November:$1,244,956


A colleague of my mine suggest summarize tables. I am going to look into that. I do think this is a possible solution but might be a bit of overkill for what I am looking for. 

v-danhe-msft Super Contributor
Super Contributor

Re: Difference of Revenue on Month to Month Basis

Hi @bw70316,

Based on my test, you could refer to below steps:

Sample data:

1.PNG

Create below calculated columns:

Index = RANKX('Table1','Table1'[Value],,ASC,Dense)
Value_ = var a=Table1[Index]-1
return [Value]-CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Index]=a))

Result:

1.PNG

 

Regards,

Daniel He

 

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

Re: Difference of Revenue on Month to Month Basis

First off, thanks for replying with your potential solution. Unfortunately, I did not get the desired result completely. This what I got:

 

Rev.png

How do I get the difference from month to month to demo the actual amount spent? Please ignore the fact the values are different, I added another filter. This was the equation I used for Value:


var a = 'Expenses'[Index]-1
return 'Expenses'[Expensed]-CALCULATE(SUM('Expenses'[Expensed]),FILTER('Expenses','Expenses'[Expensed]=a))
 
And this is the one I used for Index:

Index = RANKX('Expenses','Expenses'[Expensed],,ASC,Dense)

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: 179 members 1,842 guests
Please welcome our newest community members: