Carried Over Beginning Balance from Previous Period Ending Balance

[DoeJohn]

Hi All!

I have a confusion finding the right way to get carried over beginning cost value from previous period ending cost. I have read about recursion in power BI and I try to avoid it. Please suggest the most efficient way to solve this in power BI!!
I provide the sample data here as well. Please do ask if there's any question! Thanks 😀

Formula:
1. Ending Cost = ((Beginning Cost * Beginning Volume) + (Production Cost * Production Volume)) / (Beginning Volume + Production Volume)
2. Beginning Volume = Prev. month Beginning Volume + Prev. month Production Volume - Prev. month Sales Volume

Logic
Month	Beginning Cost	Beginning Volume	Production Cost	Production Volume	Sales Volume	Ending Cost (Average Cost)
Jan-23	100	37	97	20	23	98,95
Feb-23	98,95	34	101	30	50	99,91
Mar-23	99,91	14	93	34	37	95,02

Input
Month	Beginning Cost	Beginning Volume	Production Cost	Production Volume	Sales Volume	Ending Cost (Average Cost)
Jan-23	100	37	97	20	23
Feb-23			101	30	50
Mar-23			93	34	37

Expected Output
Month	Beginning Cost	Ending Cost (Average Cost)
Jan-23	100,00	98,95
Feb-23	98,95	99,91
Mar-23	99,91	95,02

[onurbmiguel_]

Hello @DoeJohn 

Can you sahre a pbix with some dummy values so i can look at the model? 

Best regards

Bruno Costa | Impactful Individual

[DoeJohn]

Hi Amitchandak,

Thanks for the reply!

However, while current month beginning cost is prev month ending cost, current month ending cost is dependent on current month beginning cost. Thus, when writing the formula I got circular dependency error since they are dependent on each other...

Thanks,
John

[amitchandak]

@DoeJohn , for the previous month you can use Time Intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.