Calculate median for each category based on its most recent time period

[kvnbn]

I have the following data in Power BI:

Date	Item	Client	Group	%
2/28/2023	Food	A	Large	35%
2/22/2023	Food	A	Large	100%
2/15/2023	Food	A	Large	35%
2/8/2023	Food	A	Large	35%
2/1/2023	Food	A	Large	35%
1/23/2023	Food	A	Large	35%
1/18/2023	Food	A	Large	35%
1/12/2023	Food	A	Large	35%
1/4/2023	Food	A	Large	35%
6/5/2022	Drink	B	Large	25%
6/3/2022	Drink	B	Large	25%
6/2/2022	Food	A	Large	35%
5/30/2022	Drink	B	Large	25%
5/20/2022	Food	A	Large	35%
4/29/2022	Food	A	Large	35%
4/7/2022	Food	A	Large	35%
3/24/2022	Food	A	Large	35%
3/11/2022	Food	A	Large	35%
2/25/2022	Food	A	Large	36%
2/10/2022	Food	A	Large	36%
1/25/2022	Food	A	Large	36%
1/14/2022	Food	A	Large	36%
12/30/2021	Food	A	Large	36%
12/3/2021	Food	A	Large	36%
11/15/2021	Food	A	Large	36%
11/3/2021	Food	A	Large	36%
10/27/2021	Food	A	Large	36%
10/13/2021	Food	A	Large	36%
9/29/2021	Food	A	Large	36%
9/22/2021	Food	A	Large	36%
9/15/2021	Food	A	Large	36%
9/8/2021	Food	A	Large	36%
9/1/2021	Food	A	Large	36%
8/26/2021	Food	A	Large	36%
8/18/2021	Food	A	Large	36%
8/11/2021	Food	A	Large	36%
8/2/2021	Food	A	Large	36%
7/21/2021	Food	A	Large	36%
7/14/2021	Food	A	Large	36%
7/7/2021	Food	A	Large	36%
6/30/2021	Food	A	Large	0%
6/23/2021	Food	A	Large	36%
6/15/2021	Food	A	Large	36%
6/8/2021	Food	A	Large	36%
6/2/2021	Food	A	Large	36%
5/25/2021	Food	A	Large	36%
5/18/2021	Food	A	Large	36%
11/5/2020	Drink	A	Large	23%
11/3/2020	Drink	A	Large	23%
11/1/2020	Drink	A	Large	23%
9/23/2020	Food	A	Large	34%
9/16/2020	Food	A	Large	34%

I am trying to calculate the median % for each Item, but only calculating from the lines in the most recent year for each Item. For example, Food has a most recent year of 2023, and Drink has a most recent year of 2022, so I only want to calculate the median % for Food from 2023, and % median for Drink from 2022.

Here are my current measures: 

Median = 
CALCULATE(
    MEDIANX(
        FILTER(
            'Table',
            'Table'[Date].[Year] = MAX('Table'[Date].[Year])
        ),
        IF(
            'Table'[%] > 0,
            'Table'[%],
            BLANK()
        )
    )
)

RecentYr = 
MAXX(
    SUMMARIZE(
        'Table',
        'Table'[Item],
        "MaxYear", MAX('Table'[Date].[Year])
    ),
    [MaxYear]
)

Median2 = 
CALCULATE(
    MEDIANX(
        FILTER(
            'Table',
            'Table'[Date].[Year] = [RecentYr]
        ),
        IF(
            'Table'[%] > 0, 
            'Table'[%],
            BLANK()
        )
    )
)

I am trying to summarize this info in a table visual. This is what it currently shows: 

It seems that using the MAX function in Median does not yield an answer for Drink because Max year is 2023 and there is no Drink in 2023. Meanwhile, Median2 does not seem to filter to the most recent year for each item, instead calculating the Median from all years. 

Desired output:

Item	Median	MostRecentYr
Drink	25%	2022
Food	35%	2023

Currently I can only get these numbers by having a slicer that selects the year, but my goal is to have a table visual that summarizes the most recent data for each Item. My raw data also has columns for Client and Group because I need to be able to filter by those two columns in slicers as well. 

Any help would be much appreciated, thank you!

[Greg_Deckler · Accepted solution]

@kvnbn Try this. PBIX is attached below signature:

Most Recent Year Median = 
    VAR __MaxYear = YEAR(MAX('Table'[Date]))
    VAR __Table = FILTER('Table', YEAR([Date]) = __MaxYear && [%] > 0)
    VAR __Return = MEDIANX(__Table, [%])
RETURN
    __Return

[kvnbn]

This is such an elegant solution, thank you!

[Greg_Deckler]

@kvnbn Thanks! It's the No CALCULATE approach. Every measure pretty much looks that way. Create a few VAR's, create a FILTERED or SUMMARIZED table, use an X Aggregator. Just works.