topic Running Balance and Adding Previous Month Balance in DAX Commands and Tips

Running Balance and Adding Previous Month Balance

amirel01 — Thu, 26 Oct 2023 18:33:25 GMT

I have calculated my running balance: if balance between available and scheduled is less than 0, than 0 otherwise the difference of available to scheduled - we can't carry over a positve balance. If you don't use it you lose it. My problem is that I then need adjusted scheduled hours to be scheduled hours + -running balance from previous month. You can see in my formula I used CALCULATE('Scheduled Hours Report'[Running Balance],PREVIOUSMONTH('Date'[Date])) but it's not pulling my running balance from the previous month. Is this because my running balance is from two different tables linked by date? If so, how do I get an adjusted scheduled hours column based on any negative difference between available and scheduled hours.

Re: Running Balance and Adding Previous Month Balance

wdx223_Daniel — Fri, 27 Oct 2023 06:32:05 GMT

can you show the code of other three measures?

Re: Running Balance and Adding Previous Month Balance

amirel01 — Thu, 02 Nov 2023 12:33:15 GMT

Thanks for your help. Below is the other formula's. Let me know if there is anything else I can provide to get more help on solving this 🙂

Running Balance =

IF([Running Total Available Hours]-[Running Total Scheduled Hours]>0,0,[Running Total Available Hours]-[Running Total Scheduled Hours])

Running Total Available Hours =

CALCULATE(

SUM('Available Hours'[Available Hours]),

FILTER(

ALLSELECTED('Date'[Date]),

ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

)

Running Total Scheduled Hours =

CALCULATE(

[Scheduled Hours],

FILTER(

ALLSELECTED('Date'[Date]),

ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

)

Re: Running Balance and Adding Previous Month Balance

wdx223_Daniel — Fri, 03 Nov 2023 00:23:34 GMT

it seams PREVIOUSMONTH filters the date range, and ALLSELECTED can not remove that filter

try to change ALLSELECTED to ALL

Re: Running Balance and Adding Previous Month Balance

amirel01 — Fri, 03 Nov 2023 10:21:54 GMT

That worked. Thank you so much!