Formula to get prior month asset balance to the FIRST selection in the month slicer

[rwong1]

I have the following DAX:

Average of Total Assets = VAR _MSD =

    MAX ( 'Date'[MonthID] )

VAR _YSD =

    MAX ( 'Date'[Year] )

VAR _LYED = _YSD - 1

VAR _MED = 12

VAR _A =

    CALCULATE (

        AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),

        FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = 12 && 'Date'[Year] = _LYED )

    )

VAR _B =

    CALCULATE (

        AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),

        FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = _MSD && 'Date'[Year] = _YSD )

    )

RETURN

   ( _A + _B) /2

For variable A, instead of getting the total assets as of december's month end for the previous year to the selection that I made, I would like it to be changed to pulling the total assets as of the prior month end to the FIRST selected month in the slicer.  For example, if I choose April, May, and June of 2023, I like the formula to pull March 31, 2023's total assets instead of pulling December 31, 2022's total assets.  If I choose May and June 2023 in the slicer, the formula should pull April 30, 2023's total asset balance.  Variable B is correct as that will pull June 30, 2023's Total assets.  Please advise.  Thanks.

[Sahir_Maharaj]

Hello @rwong1,

Can you please try this:

VAR _FirstSelectedMonth = 
    CALCULATE(MIN('Date'[MonthID]), ALLSELECTED('Date'))

VAR _YearOfFirstSelectedMonth = 
    CALCULATE(MIN('Date'[Year]), ALLSELECTED('Date'))

VAR _PriorMonth = 
    IF(_FirstSelectedMonth > 1, _FirstSelectedMonth - 1, 12)

VAR _YearOfPriorMonth = 
    IF(_FirstSelectedMonth > 1, _YearOfFirstSelectedMonth, _YearOfFirstSelectedMonth - 1)

VAR _A =
    CALCULATE (
        AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
        FILTER ( 
            ALL ( 'Date' ), 
            'Date'[MonthID] = _PriorMonth && 'Date'[Year] = _YearOfPriorMonth
        )
    )

[rwong1]

Hi Shair,

I got a syntax error.  I'm not sure why.