Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
YeldarR
Frequent Visitor

Make a total for maxx values (Balance sheet)

Hello, community. I've created Balance Sheet with the following formula:
Return actual balance =
VAR maxDate = MAX('BS_actuals'[date_key])

RETURN
MAXX(FILTER(BS_actuals,[date_key]=maxDate),[value])
 
This way, when choosing a range of dates, it didn't sum up all the balances, but returned the balance for the latest date of selected dates range. That worked for 1 level of balances. However, when I add the level above (grand level), it doesn't sum up but get the maxx amount for the entire level.
YeldarR_0-1642655738867.png

I've checked similar topic, but couldn't do the same for my case. Please help.

1 ACCEPTED SOLUTION

@YeldarR , Try like

 

Return actual balance =
VAR maxDate = MAXX(allselected('BS_actuals') , 'BS_actuals'[date_key])

RETURN
SUMX(FILTER(BS_actuals,[date_key]=maxDate),[value])

 

or

 

 

Return actual balance =
VAR maxDate = MAXX(allselected('BS_actuals') , 'BS_actuals'[date_key])

RETURN
SUMX(values(BS_actuals[Level1]), calculate( MAXX(FILTER(BS_actuals,[date_key]=maxDate),[value]) ) )

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@YeldarR , Try like

 

Return actual balance =
VAR maxDate = MAXX(allselected('BS_actuals') , 'BS_actuals'[date_key])

RETURN
MAXX(FILTER(BS_actuals,[date_key]=maxDate),[value])

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Thank you. Nope doesn't work. It works only for level1 layout, but level2 layout show maxx instead of sum.

BS actuals goes the following:

YeldarR_0-1642658820962.png

 

BS layout:

YeldarR_1-1642658820891.png

 

 

@YeldarR , Try like

 

Return actual balance =
VAR maxDate = MAXX(allselected('BS_actuals') , 'BS_actuals'[date_key])

RETURN
SUMX(FILTER(BS_actuals,[date_key]=maxDate),[value])

 

or

 

 

Return actual balance =
VAR maxDate = MAXX(allselected('BS_actuals') , 'BS_actuals'[date_key])

RETURN
SUMX(values(BS_actuals[Level1]), calculate( MAXX(FILTER(BS_actuals,[date_key]=maxDate),[value]) ) )

Thank you!

First one is correct.

Everyone who complies balance sheet, must use this command to get balances correct:

Return actual balance =
VAR maxDate = MAXX(allselected('BS_actuals') , 'BS_actuals'[date_key])

RETURN
SUMX(FILTER(BS_actuals,[date_key]=maxDate),[value])

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.