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 s...
Need Help
Tips and Tricks
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.

    • nholmes_auspits's avatar
      nholmes_auspits
      Frequent Visitor
      7 years ago

      Hi parry2k 

       

      Thanks again for your advice.  I had been using a data table, but with no success... below are some examples of how I've tried to use the data table:

       

      Open Mth = OPENINGBALANCEQUARTER(sum(inventorylog[qtyOnHand]), inventorylog[dateCreated], 'Date Table'[Date])
       
      Open Mth = OPENINGBALANCEQUARTER(sum(inventorylog[qtyOnHand]), 'Date Table'[Date], inventorylog[dateCreated])
       
      I'm still getting no results... I am somewhat new to Power BI and have spent the past two days searching blogs and forums for advice on how to get the results I'm after... I would really appreciate some direct advice on where I'm going wrong with my current approach.
       
      Thanks!