Forum Discussion

nholmes_auspits's avatar
nholmes_auspits
Frequent Visitor
7 years ago

Opening & Closing Balanaces

Hi All

 

I'm trying to return the opening and closing balances of a data set and I'm either getting the formula wrong or my data needs some corrections.

 

Below is a filtered sample of the data set... this is a small sample of the much larger fact table that has many more items and multiple instances of transactions... I want to create formulas that identify the opening and closing balance of month and quarter.

 

Please help!

Need Help
Tips and Tricks

5 Replies

    • nholmes_auspits's avatar
      nholmes_auspits
      Frequent Visitor
      7 years ago

      Hi parry2k , thanks for you assistance with this issue!

       

      I've tried using the opening/closing balance functions, but I've not been successful (I'm still relatively new to Power Bi and DAX)... maybe it's a filtering issue?

       

      I've used the following:

       

      Open Mth = OPENINGBALANCEMONTH(sum(inventorylog[qtyOnHand]), inventorylog[dateCreated])
       
      which results in the following:
       
       
      Thanks again!
       
      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        nholmes_auspits it is recommended/best practice to have date table in your model for all time intelligence related calculations.

         

        There are many posts on how to add date/calendar table into your model using DAX or Power Query. Add date table, link it with your inventory log table and then perform these calculations.