Forum Discussion

ferdri3's avatar
ferdri3
Frequent Visitor
4 years ago
Solved

Calculated Measure SUM

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 t...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
tamerj1
Community Champion
4 years ago

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] )
            )
    )
)