Measure for calculating 'new leave balance'

[RemiAnthonise]

Hi guys,

I couldn't find the right answer to my problem in other topics so I need your help.

Below you see my current situation and my desired situation. What we have is:

- Total leave. This is a calculated column and it exists of 2 fields, together they make the total leave. Further you see leave taken, total leave taken (for this year, a running measure) and the new balance. 

What I want is:

The 'total leave' on the new line has to be the 'new balance' from the previous line, so A minus B. I know it's really easy and I've done it before but I don't get it working. 

For now, I try it this way so I don't have to share my dataset because it has confidential data.

[v-lili6-msft · Accepted solution]

hi, @RemiAnthonise

After my test, you may try to this way as below:

Step1:

Add a group rank column for each Name by ProjectDate

group rank = RANKX(FILTER(Table1,Table1[Name]=EARLIER(Table1[Name])),Table1[ProjectDate],,ASC)

Step2:

Use this formula to add a new calculate column

new total leave = Table1[Total leave]-CALCULATE(SUM(Table1[Leave taken]),FILTER(Table1,Table1[Name]=EARLIER(Table1[Name])&&Table1[group rank]<EARLIER(Table1[group rank])))

or use this formula to add a new calculate measure

Measure = CALCULATE(SUM(Table1[Total leave]))-CALCULATE(SUM(Table1[Leave taken]),FILTER(ALLEXCEPT(Table1,Table1[Name]),Table1[group rank]<MAX(Table1[group rank])))

Result:

here is pbix, please try it.

https://www.dropbox.com/s/goqo9ig2v9swixg/Measure%20for%20calculating%20%27new%20leave%20balance%27....

Best Regards,

Lin

[Greg_Deckler]

You need to use EARLIER to find the previous row and grab the New Balance from it. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...