Forum Discussion

ChumaAmako's avatar
ChumaAmako
Helper I
6 years ago
Solved

Closing Balance Calculation

Hi Everyone,   I am trying to compute the Stock at hand on any date or month etc. The Stock on Hand (SOH) is calculated as = Opening Balance + Shipment - Sales "Opening Balance" refers to the clo...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

amitchandak's avatar
amitchandak
Super User
6 years ago

Create a common date calendar and join both tables. creating an opening balance like excel is a choice. So you have to start from the beginning. So cumulative measure will help

 

Cumm Shipment  = CALCULATE(SUM(Shipment [Shipment]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales  = CALCULATE(SUM(Sales [Sales]),filter(date,date[date] <=maxx(date,date[date])))

stock = [Cumm Shipment] -[Cumm Sales]

 

Have a month-year in your calendar table. That will allow you to stock at the month level

 

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • ChumaAmako's avatar
    ChumaAmako
    Helper I
    6 years ago

    Hi amitchandak 

     

    Thanks for your help, but this did not solve what I what, as it does not take into account the closing balance of the previous month

     

    See below output. As you can see Feb-20 does not take into account the previous closing balance of 300.

     

    Please click HERE to access the trial file if it would help