Forum Discussion

Sagejah9's avatar
Sagejah9
Helper III
4 years ago

Calculating Average YTD headcount

Hi, I would like to calculate the aVERAGE ytd Headcount. I have already calculated the headcount 

Headcount= CALCULATE(COUNTROWS('Employee Table'),
FILTER(VALUES('Employee Table'[Start Date]),'Employee Table'[Start Date]<=MAX('DateTable'[Date])),FILTER(VALUES('Employee Table'[EndDate]),OR('Employee Table'[EndDate]>=MIN('DateTable'[Date]),ISBLANK('Employee Table'[EndDate]))))

Any advice?
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Sagejah9 Is this per month or ? This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

    The pattern is:
    MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    etc.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, Sagejah9 

     

    May I ask if your problem has been solved? Calculating Average YTD headcount on a monthly or annual basis?

    Could you share some dummy data and draw a simple picture to show your expected visual so that i may work out with a workaround?😉

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.