topic Re: How to find the start date and end date of a maximum value? in DAX Commands and Tips

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

saavedrag12 — Mon, 06 Jun 2022 15:58:55 GMT

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

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

milanpasschier2 — Mon, 06 Jun 2022 16:06:17 GMT

Hi, can you share some sample data?

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

saavedrag12 — Mon, 06 Jun 2022 16:31:43 GMT

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)

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

tamerj1 — Mon, 06 Jun 2022 19:15:03 GMT

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

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

saavedrag12 — Mon, 06 Jun 2022 19:49:26 GMT

Hello,

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

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

tamerj1 — Mon, 06 Jun 2022 20:14:23 GMT

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

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

saavedrag12 — Mon, 06 Jun 2022 21:37:32 GMT

It's not recognizing the variable in the calculate statement

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

tamerj1 — Tue, 07 Jun 2022 02:34:18 GMT

saavedrag12

Sorry O wrote the wrong variable name. Here modied