cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nbitabc
Frequent Visitor

Dynamic Opening balance measure

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. 

Data.PNG

 

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?
 
Measure.PNG
Measure2.PNG
 
Thanks for any assistance.
 
 

 

 

 

1 ACCEPTED SOLUTION
deodatto
New Member

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

 

View solution in original post

5 REPLIES 5
deodatto
New Member

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
Frequent Visitor

Thanks @deodatto. Got it working using ISINSCOPE. 

daxer-almighty
Solution Sage
Solution Sage

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

 

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

 

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!