Compute monthly income per type of project

[XikinhoXK]

Hi,

I have the following table:

The invoice and revenue columns are cumulative and show the progress over the course of the months. I would like to have the monthly change so for example for the second row the amount invoiced was 4771 - 4733 = 38.

This table contains data from multiple projects so it should of course filter by project (column B) to compute that difference. 

In Excel this is easily done but i'm having trouble doing it in PBI.  Is there a formula that can solve this?

Thanks in advance.

[v-tangjie-msft · Accepted solution]

Hi @XikinhoXK ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create calculated columns.

Rank = var a=FILTER('Table','Table'[Project Definition]=EARLIER('Table'[Project Definition]))
return RANKX(a,[Month beginning Period],,ASC)

Column = var a=MAXX(FILTER('Table','Table'[Project Definition]=EARLIER('Table'[Project Definition])&&'Table'[Rank]=EARLIER('Table'[Rank])-1),[Revenue])
return IF(ISBLANK(a),0,a-[Revenue])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[shreyamukkawar]

Hi @XikinhoXK ,

I think this link will help you to solve your issue.

https://community.powerbi.com/t5/Desktop/Calculate-Differences-of-cumulative-values-by-factorial-gro...

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[XikinhoXK]

Hello shreyamukkawar,

Thanks for that link. I looked into it and it seems like pretty much the same probleme statement i have so tried that solution but to no avail.

As you can see below on the screenshot when I adapt the formula to my data i get 0 everywhere:

For reference here's how it worked with the original example:

I tried to pinpoint the problem but i'm afraid that i'm not able to put my finger on it exactly...

Thanks in advance