Utilizing a dynamic measure correctly

[Pseifert]

I have a dashboard that calculates headcount based on in/out punches. My employer maintains that an employee is active if he she punches in/out within 21 days. (part time large temp workforce). To get this dynamic headcount, I get this every monday when I get a new batch of weekly data.

This calculation:

Active Headcount(21 days) =

Var MaxDate =

Max('Calendar'[Date])

Return

If(

WEEKDAY ( MaxDate ) = 2,

CALCULATE (

distinctcount(Punch[PERSONNUM]),

FILTER (

ALL ( 'Calendar'[Date] ),

'Calendar'[Date] > MaxDate - 21

&& 'Calendar'[Date]<= MaxDate

)

),

BLANK ()

)

I use Attendance to get the number to get the breakdown of colorteam/shift.  

Attendance = calculate([Active Headcount(21 days)],ALLEXCEPT(ShiftColor,ShiftColor[ColorTeam],ShiftColor[Shift]),Allexcept('Calendar','Calendar'[Date]))

My issue is that once i filter by the active headcount number and the day, my table stops counting the color team numbers up and the measure 4 does not calculate the percentage of attendance as seen in picture 2.

My ask -> is it possible to create a calculation that uses the correct active headcount based on the date that is being filtered for and then use that to count up the actual number of colorteam for that shift, then divide to get the percentage attendance based on the dynamic headcount number? It seems using the 2 visual filters does not work here and I need more well thought out DAX for this to be possible.

Please see below!

[v-lid-msft]

Hi @Pseifert ,

Could you please provide more details about the measure 4? Does the x-axis of left two charts use the same time field?

Best regards,

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

[Pseifert]

MEasure 4 simply divides the two metrics there. The x-axis uses the same date field from a dimension calendar linked on a date from the punchin/punch out table

[v-lid-msft]

Hi @Pseifert ,

When you select the one bar in the top visual, you are actually select the date 9/30, then you select the 9/27 in the buttom visual, so there is no data for "count of color team", result the measure 4 is blank divide number, so measure 4 is blank too. Like following example:

Do you want to count for the team which equal to the selected active headcount?

Best regards,

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

[Pseifert]

Hello @v-lid-msft,

Yes, I want to take the active head count number that correlates to the week of the active headcount calculation. For instance, the active headcount value from 9/30 should be used for the days within the prior work week..i.e. 09/27. Is there a way to use that 09/30 value rather than filtering for that day? I do not need to select from both charts, I just need the bottom visual to utilize the correct active headcount value.