Forum Discussion
Carried Over Beginning Balance from Previous Period Ending Balance
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 |
3 Replies
- amitchandak
Super User
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=145sWhy 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-bd52912a5bd4
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. - onurbmiguel_
Power Participant
Hello DoeJohn
Can you sahre a pbix with some dummy values so i can look at the model?
Best regards
Bruno Costa | Impactful Individual
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
