Calculated Measure SUM

[ferdri3]

Hi everyone,

I've been at this for quite some hours now, so I decided to see if there is some help out here haha.

I'm working on an HR dashboard and they requested to see some teams back in time.

To keep it simple, I have 5 columns: Index column, Full name, Join date, Leave date (blank if still active employees) and FTE.

In a seperate file I keep all dates (gregorian and fyscal).

To see a list (matrix) with people who worked in a team at a point in time I used the following calculated measure to see if they are "Working" or "Not Working" on the date selected in the slicer.

Working = 
VAR Selected_Date =
    MAX ( 'DateTable'[Gregorian Date] )
RETURN
    SWITCH (
        TRUE (),
        MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
            && MAX ( 'Consolidated'[Leave Date] ) >= Selected_Date, "Working",
        MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
            && MAX ( 'Consolidated'[Leave Date] ) = BLANK (), "Working",
        "Not Working"
    )

...this part works as expected.

To give a single number in a card of the sum total, I use the following code:

Working Card = COUNTROWS(FILTER(DISTINCT('Consolidated'[Index]), [Working] = "Working"))

This works nicely as well.

The feedback I got was they would like to see the total of FTE (which can for example be 0,8 for some employees as well), not so much the amount of people (countrows).

Everything I try to sum up the FTE does not work and gives me errors as a calculated measure cannot be used in a true/false statement etc.

Anyone got any tips for this one?

Thanks a lot for you time and help!

[Anonymous · Accepted solution]

Hi  @ferdri3 ,

I created some data:

Table：

Date:

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Date'[Date])
return
IF(
    AND( _select >=MAX('Table'[Join date]) ,
    _select <MAX('Table'[Leave date])) ||
    AND( _select >=MAX('Table'[Join date]) ,
    MAX('Table'[Leave date])=BLANK()) ,"Working", "Not Working")

Working Card =
COUNTX(
    FILTER(ALL('Table'),
    [Flag]="Working"),[Full name])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[tamerj1]

Hi @ferdri3 
I assume that you are slicing by date. then plesae try

Total FTE =
SUMX (
    VALUES ( 'DateTable'[Gregorian Date] ),
    CALCULATE (
        VAR Selected_Date =
            MAX ( 'DateTable'[Gregorian Date] )
        RETURN
            SWITCH (
                TRUE (),
                MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
                    && MAX ( 'Consolidated'[Leave Date] ) >= Selected_Date, SUM ( 'Consolidated'[FTE] ),
                MIN ( 'Consolidated'[Join Date] ) <= Selected_Date
                    && MAX ( 'Consolidated'[Leave Date] ) = BLANK (), SUM ( 'Consolidated'[FTE] )
            )
    )
)