Get the largest value of the selected filter values

[Anonymous]

I have a Quarter filter with values YearQ# (see table below). If I select 4 values, (e.g. 2022Q2-2023Q1), how do I get the largest SELECTEDVALUE (2023Q1) and the second largest SELECTEDVALUE (2022Q4) in DAX?

Quarter

2022Q1

2022Q2

2022Q3

2022Q4

2023Q1

2023Q2

[Ahmedx]

why don't you want to create a separate calendar
Share some data to work with

[Ahmedx · Accepted solution]

You need to create your own calendar

Calendar = 
VAR BaseCalendar =
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
        RETURN ROW (
            "Year", YearDate,
            "Quarter_Year",YearDate & "Q"&QUARTER([Date]),
            "Sort",YearDate*100+QUARTER([Date])
    )
)

and then determine the maximum minimum

MinQuarter = VAR _MinSelect = MIN('Calendar'[Sort])
RETURN
 MINX(FILTER(ALL('Calendar'),'Calendar'[Sort]=_MinSelect),[Quarter_Year])

MaxQuarter = VAR _MinSelect = MAX('Calendar'[Sort])
RETURN
 MINX(FILTER(ALL('Calendar'),'Calendar'[Sort]=_MinSelect),[Quarter_Year])

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhgrLydtoJOrm3tgv?e=KHDnMg

[Anonymous]

This works! However, the Quarter column I provided is part of a larger table with more columns and corresponding values that I will be needing for creating more DAXs. Is there a way where I turn my Quarter column into a Calendar data (so that the values from other columns of specific Quarter values from my original table remain)?

To visualize:

Table:

Quarter	Phone	PhoneSold
22Q4	A	1
22Q4	A	3
22Q4	B	2
22Q4	B	2
23Q1	A	5
23Q1	B	1
23Q1	A	3
23Q1	B	3

Resulting Table:

Filtered on this visual: 22Q2, 22Q3, 22Q4, 23Q1 (or some other 4 quarters)

Phone Model	Sum of PhonesSold (for the largest/recent quarter - 23Q1)	Sum of PhonesSold (for the second largest/recent quarter - 22Q4)	Difference	Sparkline (hence the filter)
A	8	3	5	(sparkline in 4 quarters)
B	4	4	0	(sparkline in 4 quarters)

I'm planning to update the resulting table by only changing the Quarter filters and not creating more DAX.