Forum Discussion

StefRenneboog91's avatar
StefRenneboog91
Regular Visitor
5 years ago
Solved

Beginner level - Filter / Slicer / Sum

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) - these 2 dates are stating the validity of the record and not if an employee is active - 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 (slicer date I selected) because between 01/10/2016 and 31/12/9999 this user has stat2 = 3 or in SAP terms : 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
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    StefRenneboog91 , are these two dates detected as date. The formula should work if something is selected.

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    StefRenneboog91 , are these two dates detected as date. The formula should work if something is selected.

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.