Dynamic Opening balance measure

[nbitabc]

Hello all,

We have a monthly aggregated snapshot table as data source, and I am trying to come up with a dynamic begining balance measure. 

This is the structure of our data with entry for each account every month. In this scenario, Western account was started in March 2021. 

Requirement is to create an Opening_balance measure which will default to January balance if Year-Month is not selected in the table. I have created a measure with this code - 

Op_Bal_Measure = CALCULATE( SUM(Sheet2[Opening Balance]),

                                                                     FIRSTNONBLANK(Sheet2[YearMonth],

                                                                                                    COUNTROWS(Sheet2)))

This works fine if Year-Month is selected but gives March opening balance of $800 for Western account for the Year. This should be 0 or blank for Western as Opening balance for year is always as of January. If Month is selected then it should be Opening balance as of Month.

How do we achieve this in a single measure?

Thanks for any assistance.

[deodatto · Accepted solution]

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

[nbitabc]

Thanks @deodatto. Got it working using ISINSCOPE. 

[daxer-almighty]

I would create the formula for you but I never do this in incorrect models. Your model looks to be a one-table model. This is simply wrong. You'll be producing wrong numbers in no time, even with correct formulas. Please read this Why one-table models will produce WRONG NUMBERS - Microsoft Power BI Community

So, first you should refactor the model into a correct one. Then it'll be much easier to create the measure you're after. But it would be even easier to change the model so that you don't have to calculate anything but just retrieve figures from a table. Just make a table of opening balances so that each account has an entry for each YearMonth. Then you won't have to calculate anything or... your formula above will work without any changes. The formula itself is not written optimally. Also, there are DAX functions that let you calculate opening and closing balances, for instance, OPENINGBALANCEMONTH – DAX Guide

[nbitabc]

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

[daxer-almighty]

@nbitabc 

"... 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.