Forum Discussion

T_JF2022's avatar
T_JF2022
Frequent Visitor
4 years ago
Solved

Correct Calculate

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
NameUniqIDCost CenterFTEDate
Ronaldocr71000131.01.2022
NeymarjNY11000,931.01.2022
MessiLMS11000,7531.01.2022
MbappekMP1100131.01.2022
HaalandeHL1010131.01.2022
SterlingjST10200,931.01.2022
KroostKO13000,531.01.2022
Ronaldocr71000128.02.2022
NeymarjNY11000,928.02.2022
MessiLMS  28.02.2022
MbappekMP1100128.02.2022
HaalandeHL1010128.02.2022
SterlingjST10200,928.02.2022
KroostKO13000,528.02.2022
Ronaldocr71000131.03.2022
NeymarjNY11000,931.03.2022
MessiLMS  31.03.2022
MbappekMP1100131.03.2022
HaalandeHL1010131.03.2022
SterlingjST10200,931.03.2022
KroostKO13000,531.03.2022
LewandowskiRL91310131.03.2022
Small example how the HR Report Looks like.
 
Thanks for your help 😉
 
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • serpiva64's avatar
    serpiva64
    Solution Sage
    4 years ago

    Hi,

    try

    EMPCount = Calculate(Count('Table'[Unique Id]), 'Table'[Date] = MAX('Table'[Date])&&NOT('Table'[Cost Center]=BLANK()))
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.