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

[saavedrag12]

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

Measure_Peak_HC= SUMX(SUMMARIZE(DisciplineTable, DisciplineTable[discipline], "Max HC", MAX(HeadcountTable[Headcount])), [Max HC])

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!!

[tamerj1]

Hi @saavedrag12 

please try

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

[saavedrag12]

Hello, 

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

[tamerj1]

@saavedrag12 

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

then please try 

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

[saavedrag12]

It's not recognizing the variable in the calculate statement 

[tamerj1 · Accepted solution]

@saavedrag12 

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

[saavedrag12]

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)

[milanpasschier2]

Hi, can you share some sample data?