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
Community Support Team
Community Support Team

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...


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. 

Community Support Team
Community Support Team

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)