Forum Discussion

YeldarR's avatar
YeldarR
Frequent Visitor
4 years ago
Solved

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),[v...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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]) ) )

amitchandak's avatar
amitchandak
Super User
4 years ago

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]) ) )

YeldarR's avatar
YeldarR
Frequent Visitor
4 years ago

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])