Max of before the selected month

[TWorku]

Good morning
I have a summarized table as follows. 

I can get the max like this 

Max Opened Accts = MAXX(KEEPFILTERS(VALUES('Summarized Table'[YearMonthTxt]) ),

     CALCULATE(SUM('Summarized Table'[Accounts]) ) )

which is 165 for product 1 and 244 for product 2

The user want to use a date slicer. and when they select lets say Feb 2022, they want to see the max before feb 2022. which is 142 for product 1. 

can someone help me with this task? 

[Anonymous]

Try this:
(assuming you have a relationship between your Summarize Table and the callendar)

Measure = 

calculate(

Max( Table[Product 1] ),

Table[Date] < max( 'Calendar'[Date] )

)

if you don't have a Date collumn in your Table, and only a YearMonth collumn, there are ways to go around this but I would suggest adding a Date Collumn as per best practice.

[tackytechtom · Accepted solution]

Hi @TWorku ,

Maybe this one here, gets you further 🙂

Max Opened Accts = 
VAR _selectedDate =  MAX ( Date[Date] )
RETURN
MAXX (
    'Summarized Table',
    CALCULATE (
        SUM ( 'Summarized Table'[Accounts] ) 
    ),
    REMOVEFILTERS ( 'Summarized Table' ),
    Date[Date] < _selectedDate
)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[speedramps]

Hi TWorku

Slicers do what they say on the tin. They slice the data !!!

If you slice the date for Feb 2022 then the data will only have  Feb 2022 and you cant aggrrate other months.

The solution is to have a detacthed period pick list. (with no relationships)

So when the user clicks the slicer they select a date but do not slicer the data.

Max Opened Accts

VAR mypickdate = MIN(picklist[date])
RETURN
CALCULATE(
= MAX(yourtable[value],

     yourtable[date] < mypickdate

)

 

Please click thumbs up and the accept as solution buttons. Thank you ! 😀