Skip to main content
cancel
Showing results for 
Search instead 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

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

 

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

 

 

1 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

 

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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

 

 

Hello, 

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

saavedrag12_0-1654544950026.png

 

 

@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_0-1654551404676.png

It's not recognizing the variable in the calculate statement 

@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
Frequent Visitor

saavedrag12_0-1654532844651.png

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)

Hi, can you share some sample data?

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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