Correct Calculate

[T_JF2022]

I've some problems to calculate the correct value.

Context - Every month i receive a report with a list of employees with the column Name, UniqID, CostCenter, FTE & Report Date. Unfortunately, this list also includes employees who are no longer working for us. Departed employees have no entry in the FTE & Cost Center row.

Problem -

1. I want to calculate the Headcount (how much different People work for us), without the people who have no entry in the line "CostCenter" & "FTE".

2.  For FTE count i've to take the sum of the different FTE values. As an KPI visualisation, it takes the FTE values of all Periods, but I want that it only shows the amount for the last period, if I havent filtered for an specific period. 

3. Overall Im asking for a way, to only show the Period for KPI's which changes dynamic if the enduser switches to an other Period. But default should be Last Period. 

My caculation for the Headcount 

EMPCount = Calculate(Count([Unique Id]), Filter(all('DIM_Date'[Month Number]), 'DIM_Date'[Month Number] = Max('DIM_Date'[Month Number])))

isn't working. It Counts all UniqID still if they dont have an entry at Costcenter &/or FTE.

Spoiler

Name	UniqID	Cost Center	FTE	Date
Ronaldo	cr7	1000	1	31.01.2022
Neymar	jNY	1100	0,9	31.01.2022
Messi	LMS	1100	0,75	31.01.2022
Mbappe	kMP	1100	1	31.01.2022
Haaland	eHL	1010	1	31.01.2022
Sterling	jST	1020	0,9	31.01.2022
Kroos	tKO	1300	0,5	31.01.2022
Ronaldo	cr7	1000	1	28.02.2022
Neymar	jNY	1100	0,9	28.02.2022
Messi	LMS			28.02.2022
Mbappe	kMP	1100	1	28.02.2022
Haaland	eHL	1010	1	28.02.2022
Sterling	jST	1020	0,9	28.02.2022
Kroos	tKO	1300	0,5	28.02.2022
Ronaldo	cr7	1000	1	31.03.2022
Neymar	jNY	1100	0,9	31.03.2022
Messi	LMS			31.03.2022
Mbappe	kMP	1100	1	31.03.2022
Haaland	eHL	1010	1	31.03.2022
Sterling	jST	1020	0,9	31.03.2022
Kroos	tKO	1300	0,5	31.03.2022
Lewandowski	RL9	1310	1	31.03.2022

Small example how the HR Report Looks like.

Thanks for your help 😉

[Anonymous · Accepted solution]

Hi @T_JF2022 ,

Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.

Please try below steps:

1. below is my test table

Table:

Table2:

Table 2 =
CALENDAR ( FIRSTDATE ( 'Table'[Date] ), LASTDATE ( 'Table'[Date] ) )

2. create a measure and add it to card visual

Measure =
VAR max_date =
    MAXX ( ALL ( 'Table 2' ), 'Table 2'[Date] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Cost Center] <> BLANK ()
            && 'Table'[FTE&Report ] <> BLANK ()
            && 'Table'[Date] = max_date
    )
RETURN
    COUNTROWS ( tmp )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[serpiva64]

Hi,

try

EMPCount = Calculate(Count('Table'[Unique Id]), 'Table'[Date] = MAX('Table'[Date])&&NOT('Table'[Cost Center]=BLANK()))