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

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

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

Proud to be a Super User!

3 REPLIES 3
Super User III

Hi @faisalt

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

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors