Another Measure Issue - the maximum of data for groups of weeks

[Anonymous]

Hi there,

Our resource plan has multiple counts for departments for weeks over a number of years, broken up by department.

I am trying to come up with the week that has the highest count, so basically when we are at our peak.

I have this

Max Crew = CALCULATE(MAX(CrewPlanCondensed[Count]),ALL(CrewPlanCondensed))

But that shows the maximum single value for any department, I need to group this down by week.. i.e. sum the count on a week by week basis to find the value of the highest week.

Thanks

[Anonymous]

Thanks for the replies, sorry for the excessive delay - few things distracting me away from this currently.

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @Anonymous ,

Try this.

Measure = 
MAXX(
    FILTER(
        ALL(CrewPlanCondensed),
        [Week] = MAX([week]) && [Department] = MAX([Department]) 
    ),
    [Count]
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a date table , with Week and Week Rank, refer my blog for that

Then create two measure like 

M1 = calculate(Sum(CrewPlanCondensed[Count]))
new measure =
var _1 = maxx(values(Date[Year Week]),[M1])

return
maxx(filter(values(Date[Year Week]), [M1] =_1),[Year Week])

For week related info refer my blogs

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8