How to find Min and Max within the group in power bi

[Anonymous]

I am trying to find dax formula to show in the card to display the season range between the year and week.I want get the min week in the min year and max week and max year :

I have the below table:

I want to show the card like below:

"Between 2020-33 and 2021-1"

But the below formula shows as : "Between 2020-1 and 2021-52"

Can any one please help me to find the dax to show the correct card ..My current formula is like below:

Min_Season = CALCULATE(Min('Year-Week_Table'[Year]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1)) & "-" & CALCULATE(Min('Year-Week_Table'[Week]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1))&" and " & CALCULATE(MAX('Year-Week_Table'[Year]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1)) & "-" & CALCULATE(max('Year-Week_Table'[Week]),FILTER('Flag-Year-Week_Table','Flag-Year-Week_Table'[STD_CY_Ind]=1))

I want to show the card like below: "Between 2020-33 and 2021-1"

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download sample PBIX with data, measures and visuals.

Based on your data the MIN year is 2019 and the MIN week in 2019 is 52.  Likewise the MAX year is 2021 and the MAX week in that year is 1.

Therefore shouldn't your message be Between 2019-52 and 2021-1 ?

The MIN Year-Week measure is

Min Year-Week = MIN('year-week'[Year]) & "-" & CALCULATE( MIN('year-week'[Week]), FILTER('year-week', 'year-week'[Year] = MIN('year-week'[Year])))

The MAX Year-Week measure is 

Max Year-Week = MAX('year-week'[Year]) & "-" & CALCULATE( MAX('year-week'[Week]), FILTER('year-week', 'year-week'[Year] = MAX('year-week'[Year])))

Combine the two in your card

Card Msg = "Between " & [Min Year-Week] & " and " & [Max Year-Week]

To give

 

Regards

Phil

[V-lianl-msft]

Hi @Anonymous ,

Sorry, your description is difficult to make people clear where the problem lies.

You could see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

You could directly describe the calculation logic instead of the formula you use, and share your sample .pbix.

Please remove any sensitive data before uploading.

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

[amitchandak]

@Anonymous , Create a column like

Year week =

var _yrw = ([year]*100 + [week] ) &""

return

left(_yrw,4) & "-" & right(_yrw,2)

Now min and max of this column is easy

a new meausre

measure = "Between " & min([Year week]) &" and " & max(Table[Year week])