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
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!