Forum Discussion

Pseifert's avatar
Pseifert
Frequent Visitor
6 years ago

Utilizing a dynamic measure correctly

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

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      Pseifert
      Frequent Visitor
      6 years ago

      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's avatar
        v-lid-msft
        Community Support
        6 years ago

        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.