Forum Discussion
Dynamic Opening balance measure
hi friend,
you try use a isonvalue or isinscope? you have two modes to calculate, month and year, use a isinscope in you meansure. anda create a meansure to calculate separate values to last month, and sum of values to use more effective.
best regards
Hi daxer-almighty Totally agree with your model suggestion.
However, this is just a dummy file I quickly created to demo the issue. The actual model is a Star Schema model with 3 fact table and close to 30 dimension tables,
We have around 8 million accounts and model has close to 25 different Opening balances, so creating a separate Opening balance table is not an efficient solution in our case.
OPENINGBALANCEMONTH is definitely a good option, but we just wanted to expore alternative way of accomplishing this without dependancy on a date table, as we did in our legacy reporting tool before recently switching to PowerBI
"... so creating a separate Opening balance table is not an efficient solution in our case."
Well, I beg to differ. It's always more efficient to have a table (even a big one) that has figures precalculated in it than to have to calculate them in DAX. Retrieval is always much faster than calculations. For the very simple reason that retrieval is performed by the Storage Engine and calculations must go through the Formula Engine (with the exception of some very primitive calculations). Please read about these two concepts to understand exactly what I'm talking about.
But after all... You'll look for the right solution I'm talking about as soon as your figures start to appear with delays.