cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TWorku
Frequent Visitor

Max of before the selected month

Good morning
I have a summarized table as follows. 

TWorku_1-1651769805456.png

 

 

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? 

 

1 ACCEPTED SOLUTION
tomfox
Community Champion
Community Champion

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/

View solution in original post

3 REPLIES 3
Parreirao2
Helper I
Helper I

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.
tomfox
Community Champion
Community Champion

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
Super User
Super User

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 ! 😀

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors