cancel
Showing results for
Did you mean:
Frequent Visitor

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

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"

1 ACCEPTED SOLUTION
Super User III

Hi @faisalt

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

3 REPLIES 3
Super User III

Hi @faisalt

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Community Support

Hi @faisalt ,

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:

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

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

Super User IV

@faisalt , 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])

Proud to be a Super User!

Announcements