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!