Beginner level - SUMX - FILTER - SELECTEDVALUE

[StefRenneboog91]

Hey guys,

Thanks a lot already for the one who is willing to help me.

As you can see in the screenshot, in the table we're having a personnel number (PERNR) , a begin date (BEGDA), an end date (ENDDA) and stating if a user is active or not (STAT2 - if stat 2 = 3 then user is active, if not equal to 3 then he is not active).

The measure I created at this moment looks like this : 

Headcount =

SUMX(

Filter(

PA0000,

PA0000[BEGDA] <= SELECTEDVALUE('Date'[Date],TODAY()) &&

PA0000[ENDDA] >= SELECTEDVALUE('Date'[Date],TODAY())

),PA0000[STAT2]/3

)

I created a slicer in the report with the date but if I create a report with PERNR + Measure Headcount it's returning 0 records.

To give the example PERNR 200001 (screenshot) should have value 1 for date = 01/01/2020 because BEGDA < 01/01/2020 and ENDDA > 01/01/2020 and STAT2 = 3

I hope someone can help me out with this question.
Thanks a lot again.

KR,

Stef

[v-yingjl · Accepted solution]

Hi @StefRenneboog91 ,

I found that your [BEGDA] and [ENDDA] column is a whole number type instead of a date type so I would suggest you to change these data type of columns to date firstly.

Then, based on your description, you can create this measure to count:

Count =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[PERNR] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[STAT2] = 3
                && 'Table'[BEGDA] <= SELECTEDVALUE ( 'Date'[Date], TODAY () )
                && 'Table'[ENDDA] >= SELECTEDVALUE ( 'Date'[Date], TODAY () )
        )
    )
RETURN
    IF ( ISBLANK ( _count ), 0, _count )

Attached a sample file in the below, hopes to help you.

Beginner level - SUMX - FILTER - SELECTEDVALUE.pbix 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@StefRenneboog91 , see my HR blog on the related topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or https://www.youtube.com/watch?v=e6Y-l_JtCq4