Row Difference BY Period

[Sime]

Hi All,

I am calculating Earned Value by period based on the % of movement mulitplied by a budget figure.
On occassion the budget figure changes and therefore the total EV is not accurate due to the change in the budget at that given time.

What I would like to do is include a column that calculates the difference between the rows so I can mulitply this by the previous % value and then sum that with the total EV when applicable.

A sample of the data is posted below:

Period	Previous %	Overall %	Budget	Difference
30/09/2023	0	0	0	0
31/10/2023	0	20	401,932.00	401,932.00
30/11/2023	20	52	669,156.00	267,224.00
31/12/2023	52	74	669,156.00	0

Any help would be appreciated. Thank you.

Edit: @Ashish_Mathur @Greg_Deckler Thank you for your responses.  I have updated the Difference column to reflect the expected results as requested.

[Ashish_Mathur · Accepted solution]

Hi,

Assuming you have a Calendar Table with a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table AND to your visual, you have dragged Month and Year from the Calendar Table, write these measures:

Previous month budget = calculate([Budget],previousmonth(Calendar[date]))

Diff = [Budget]-[Previous month budget]

Hope this helps.

[Anonymous · Accepted solution]

Hi @Sime 

If your source data table is like the sample data provided, you can create a calculated column with below DAX:

Difference = 
var curMonth = 'Table'[Period]
var preBudget = CALCULATE(SUM('Table'[Budget]),ALL('Table'),'Table'[Period]=EOMONTH(curMonth,-1))
return
'Table'[Budget] - preBudget

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[Sime]

Hi Ashish, I have added the expected "Difference" values to the sample table data below and to the original thread:

Period	Previous %	Overall %	Budget	Difference
30/09/2023	0	0	0	0
31/10/2023	0	20	401,932.00	401,932.00
30/11/2023	20	52	669,156.00	267,224.00
31/12/2023	52	74	669,156.00	0

Any help would be appreciated. Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Assuming you have a Calendar Table with a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table AND to your visual, you have dragged Month and Year from the Calendar Table, write these measures:

Previous month budget = calculate([Budget],previousmonth(Calendar[date]))

Diff = [Budget]-[Previous month budget]

Hope this helps.

[Greg_Deckler]

@Sime See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous