Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 -
Solved! Go to 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
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
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
"... 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |