Number of employees in DAX

[pedroccamara]

Hey guys
I wonder if you can help me here.
I have this table with codname and LastProccessDate. Attach.

If you count the people that LastproccessDate = 31/12/2020 you will have 23.
Although you don’t have any with LastproccessDate = 30/11/2020, you should have the same 23. And if you going down to july it’s the same number, except july that you should have 24 and in june, 26. In the 31st of january of 2019 you should have 32 people.
Is there any dax formula to do this?
I hope i was clear enough.
Thanks a lot
Pedro
Book1.xlsx (10.1 KB)

[Rens]

Hi Pedro,

The following should work for you:

Count =
CALCULATE (
COUNT ( Table1[PersonCode] ),
FILTER (
ALLSELECTED ( ‘Table1’ ),
Table1[Last Process Date] >= MAX ( Table1[Last Process Date] )
)
)

[pedroccamara]

Hey @Rens
Thanks a lot for your answer, but…i have to do this measure with USERELATIONSHIP and i can’t add to it. Can you help me? Where do i put this? Cose i’ve been trying and it seems wrong every time

USERELATIONSHIP( ‘Tab Dates’[Date], ‘Tab Employee’[Las Proccess Date])

I never understand what comes first and last…

Thanks a lot
Pedro

[Rens]

Hi @pedroccamara

Ah yes, USERELATIONSHIP always complicates matters …
Remembering you (and myself!) that it only needs to be used when you have an inactive relationship between the tables. If the relationship is active, we do not need USERELATIONSHIP.
Anyway, assuming you have an inactive relationship, we add it also in the CALCULATE and so the following code should work:

Count2 =
CALCULATE( COUNT ( Table1[PersonCode] ),
FILTER (ALLSELECTED (‘Invoked Function’),
‘Invoked Function’[Date] >= MAX ( ‘Invoked Function’[Date] )
),
USERELATIONSHIP(‘Invoked Function’[Date], Table1[Last Process Date]
)
)

[pedroccamara]

Hey @Rens
It’s not working, at least the way you’ve showed me, which exactly what i need. Here’s my measure:

Employees MoM =
CALCULATE (
COUNT ( ‘Tab Employee’[Codigo] ),
FILTER (
ALLSELECTED ( ‘Tab Employee’ ),
‘Tab Employee’[DataUltProcessamento]
>= MAX ( ‘Tab Employee’[DataUltProcessamento] )
),
USERELATIONSHIP ( ‘Tab Dates’[Date], ‘Tab Employee’[DataUltProcessamento] )
)
Let me show what i got, with any filter in this visual.

Maybe something to do with those parentisis of the measure?

[Rens]

Hi Pedro,

In attachment the solution allselected.pbix (91.5 KB) in a Power BI file I just created. Hope this helps!

[pedroccamara]

Thanks a lot @Rens