Problem with Opening / Closing balance. They need to be base for follow up measures.

[AndrejZitnay]

Hello all,

I have my table with opening and closing balance.

It does works fine however I need to use sum of [opening balnce] & [monthly new] for chain of follow up measures.

Follow up measures seems to be fine on monhly basis however totals are wrong.

Here is one example of dataset.

My follow up Measures 2 & 3 are fine on monthly basis but total is wrong. 

I need to get totals right. 

Can you help me out with some workaround for opening balance?
I need to do sum out of that measure. It can be used only for follow up measures.

I can leave original opening balance in my talbe.

[v-shex-msft]

Hi @AndrejZitnay,

It seems like a common date range analysis requirement, you can take look at the following blog 'start date', 'end date' parts if they suitable for your requirement:

Before You Post, Read This #'start date', 'end date' 

Regards,

Xiaoxin Sheng

[amitchandak]

@AndrejZitnay , Table might not have a continuous date that can be an issue in the opening balance

Try like

Opening balance = CALCULATE([All measure ],filter(allselected(Table),Table[Date] <=eomonth(max(Table[Date]),-1)))

[AndrejZitnay]

Hello @amitchandak ,

thanks for your help.

What do you mean byt coninuous date? 
My calendar is driven by 1st day of each month and they are not gaps in the month.

OpeningBalance = CALCULATE([Balance],FILTER(ALLSELECTED(MonthTable),MonthTable[Month]<=EOMONTH(MAX(MonthTable[Month]),-1)))

I have same outocme as above screenshot.

Opening and closing balance is correct. With new measure I can still achieve correct figures.

Problem is with follow up measurments. 

I can't get right total.

Opening / Closing balance isn't required to have total however [opening balnce] + [monthly new] needs to have total as this is first mesurment in long chain of measurments. 

Total isn't correct after  [opening balnce] + [monthly new] hence I think that opening or closing balance needs some work around.

I sitll don't have correct total for Follow up measure 2 & 3

Andrej

[amitchandak · Accepted solution]

@AndrejZitnay , I would expected total same as last month opening balance

else we can try

OpeningBalance = CALCULATE(sumx(values(MonthTable[Month]),[Balance]),FILTER(ALLSELECTED(MonthTable),MonthTable[Month]<=EOMONTH(MAX(MonthTable[Month]),-1)))

if this did not work line-level but work at grand total level, we can use isinscope to switch formula  https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[AndrejZitnay]

Hello @amitchandak ,

My opening blanace is same as closing balnace from last month. That is working. 

Problem is elswhere.

Totals in Follow up Measures aren't showing right total.

Follow up measure 1 is (Openning Balance + Monhlty New) /100

Follow up Measure 2 & 3 are linked to Follow up measrue 1 

Follow up measure 3 is Follow up Mesure 1 * ratio

Follow up measure 2 is 7% of measure 3

You can see that sum of Openning Balance & Monthly new isn't correct it should be 219,187 and not 112,425 as on table. 

[amitchandak]

@AndrejZitnay , Try the sumx way on those measures. 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AndrejZitnay]

Hello @amitchandak ,

I have tried sumx but same outcome.

I have created sample PBIX

https://ulozto.cz/tamhle/09aKv8imMdia/name/Nahrano-17-2-2021-v-16-26-19

All formulas are the same as in my real data only my table 2 & 3 have much more rows.

For example Deduction 4 for October 2020 might be in 25 rows but total is 20

You can see that my Measure 1, 2 & 3 have correct figures in rows.

But my total is wrong.

This is my biggest problem which affect whole model.

thanks.

Andrej