cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

How to find the start date and end date of a maximum value?

Hello,

I'm trying to figure out a way for Power BI to call out a a start and end date of a maximum value. So for context, I am working with headcount data. Our headcout over a duration of a project looks a lot like a bell curve, however, once the peak headcount is reached, it stays at that amount for a number of weeks before declining again. I want to create a dynamic measure that calls out the individual weeks that the peak starts and ends.

Here is our formula to get the actual peak headcount amount (sum of headcount divied up by discipline):

Now we're trying to create a measure that finds the start and end date (work week) of that peak HC.

Any tips or tricks would help!!

1 ACCEPTED SOLUTION
Super User

Sorry O wrote the wrong variable name. Here modied

Peak Start & End =
VAR MaxCount = [Measure_Peak_HC]
VAR PeakWeeks =
FILTER (
VALUES ( 'Date'[Column_WW_YR] ),
CALCULATE ( SUM ( TableName[HeadCount] ) ) = PeakWeeks
)
VAR MinWeek =
MINX ( PeakWeeks, 'Date'[Column_WW_YR] )
VAR MaxWeek =
MAXX ( PeakWeeks, 'Date'[Column_WW_YR] )
RETURN
"Peak Count of " & MaxCount & " within " & MinWeek & " - " & MaxWeek

7 REPLIES 7
Super User

Peak Start & End =
VAR MaxCount = [Measure_Peak_HC]
VAR PeakWeeks =
FILTER ( VALUES ( 'Date'[Column_WW_YR] ), [HeadCount] = PeakWeeks )
VAR MinWeek =
MINX ( PeakWeeks, 'Date'[Column_WW_YR] )
VAR MaxWeek =
MAXX ( PeakWeeks, 'Date'[Column_WW_YR] )
RETURN
"Peak Count of " & MaxCount & " within " & MinWeek & " - " & MaxWeek

Frequent Visitor

Hello,

It doesn't allow me to select the headcount column when creating the second variable :

Super User

I thought head count is a measure. So it is just a coulmn summarized by sum?

Peak Start & End =
VAR MaxCount = [Measure_Peak_HC]
VAR PeakWeeks =
FILTER (
VALUES ( 'Date'[Column_WW_YR] ),
CALCULATE ( SUM ( TableName[HeadCount] ) ) = PeakWeeks
)
VAR MinWeek =
MINX ( PeakWeeks, 'Date'[Column_WW_YR] )
VAR MaxWeek =
MAXX ( PeakWeeks, 'Date'[Column_WW_YR] )
RETURN
"Peak Count of " & MaxCount & " within " & MinWeek & " - " & MaxWeek
Frequent Visitor

It's not recognizing the variable in the calculate statement

Super User

Sorry O wrote the wrong variable name. Here modied

Peak Start & End =
VAR MaxCount = [Measure_Peak_HC]
VAR PeakWeeks =
FILTER (
VALUES ( 'Date'[Column_WW_YR] ),
CALCULATE ( SUM ( TableName[HeadCount] ) ) = PeakWeeks
)
VAR MinWeek =
MINX ( PeakWeeks, 'Date'[Column_WW_YR] )
VAR MaxWeek =
MAXX ( PeakWeeks, 'Date'[Column_WW_YR] )
RETURN
"Peak Count of " & MaxCount & " within " & MinWeek & " - " & MaxWeek

Frequent Visitor

For example: I want to create a measure that will call out the start date (and end date) of the peak HC (WW circled in green). So in this case, if I were to put the measure on the card, it will call out the value/date : WW 37.22 (this is from our calendar table)

Helper I

Hi, can you share some sample data?

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors