How to get Max Value for a selected month and year

[Anonymous]

Hi everyone,

I need help on how to get the value of the last month in a selected year in powerbi.

So I have a table that shows the month, the year and sales values and I want to create a report that will have two filters Year and Month. However, I want a situation whereby if the user select year and month filter it will show the sales value from the table but if the user select only year, it will select the value from the last month for that year.

For instance, if the table is this:

Year	Month	Sales Amount
2019	2019 October	1000
2019	2019 November	900
2019	2019 December	1400
2020	2020 January	1200
2020	2020 February	980

If the user select 2019 in year and select All for month, the report should show

Year             Sales

2019            1400     (which is the values for the last month in the selected year)

if they select 2019 in year and Novermber in Month

Year        Sales

2019        900            (which is the value for the selected month and year)

Thanks in advance for the help.

[amitchandak · Accepted solution]

@Anonymous , if you date or create a date like

 Date  =  "01-"&[Month] & "-" [Year]

Try a measure like

lastnonblankvalue(Table[date],sum(Table[Sales Amount]))

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I build a table like this to have a test.

Build a calender table to build slicer.

Slicer = CALENDARAUTO()

Year = YEAR(Slicer[Date])

Month = MONTH(Slicer[Date])

Result:

Build two measures to achieve your goal.

M_Year = 
IF(ISFILTERED(Slicer[Year]),CALCULATE(MAX('Table'[Year]),FILTER(ALL('Table'),'Table'[Year]=MAX(Slicer[Year]))),BLANK())

Sale Amount = 
VAR _MonthAmount =
    CALCULATE (
        SUM ( 'Table'[Sales Amount] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month].[MonthNo] = MAX ( Slicer[Month] )
                && 'Table'[Year] = MAX ( Slicer[Year] )
        )
    )
VAR _LastAmount =
    CALCULATE (
        SUM ( 'Table'[Sales Amount] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month]
                = CALCULATE (
                    LASTDATE ( 'Table'[Month] ),
                    FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( Slicer[Year] ) )
                )
        )
    )
RETURN
    IF (
        ISFILTERED ( Slicer[Year] ),
        IF ( ISFILTERED ( Slicer[Month] ), _MonthAmount, _LastAmount ),
        BLANK ()
    )

Result:

Only select Year 2019:

Select Year 2019 and Month 11:

You can download the pbix file from this link: How to get Max Value for a selected month and year

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak · Accepted solution]

@Anonymous , if you date or create a date like

 Date  =  "01-"&[Month] & "-" [Year]

Try a measure like

lastnonblankvalue(Table[date],sum(Table[Sales Amount]))