cancel
Showing results for
Did you mean:
Highlighted
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
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:

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:

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

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

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.

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:

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:

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

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)

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 179 members 1,842 guests
Recent signins: