Calculating a Historic Retrospective Balance from a Starting Balance

[jjmanx]

Hi all,

Long time reader first time poster. I'm relatively new to DAX so I'm hoping you may be able to help me with a problem I'm stuck on.

I need help calculating historic per month balances, but by only using the current balance as it stands today plus the credit and debit totals which occur each month. 

I can work out the balance for this month and the previous month, but I can't figure out how to repeat the process to discover the balances for months prior to that. 

Please help! 

[amitchandak · Accepted solution]

@jjmanx , This should same as inventory or reverse

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

or

Inventory / OnHand
[Todays Inventory] - CALCULATE(SUM(Table[Ordered]),filter(date,date[date] >=maxx(date,date[date]))) + CALCULATE(SUM(Table[Sold]),filter(date,date[date] >=maxx(date,date[date])))

refer this

https://community.powerbi.com/t5/Community-Blog/Inventory-Management-and-Analysis-Using-Power-BI/ba-...

[jjmanx · Accepted solution]

So this may have been what you were getting at @amitchandak but I've found this post and, after tweaking it slightly, found that it got me to where I needed.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-Historical-value-based-on-value/td-...

[jjmanx · Accepted solution]

So this may have been what you were getting at @amitchandak but I've found this post and, after tweaking it slightly, found that it got me to where I needed.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-Historical-value-based-on-value/td-...

[jjmanx · Accepted solution]

And for anyone who's interested. 

I was able to upgrade this from a calculated column to its own sliceable measure.

[amitchandak · Accepted solution]

@jjmanx , This should same as inventory or reverse

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

or

Inventory / OnHand
[Todays Inventory] - CALCULATE(SUM(Table[Ordered]),filter(date,date[date] >=maxx(date,date[date]))) + CALCULATE(SUM(Table[Sold]),filter(date,date[date] >=maxx(date,date[date])))

refer this

https://community.powerbi.com/t5/Community-Blog/Inventory-Management-and-Analysis-Using-Power-BI/ba-...

[jjmanx]

Thank you Amit but unfortunately I don't think that's going to to work.

Unless I'm mistaken, I think these calcs are working out the difference between orders and sales for any given month but then calculating that against today's account balance. 

What I need is that month's difference calculated against its previous month. 

I hope that makes sense.