Filter issue with "<="

[Allie]

Hello,

I am trying to get a chart that will show me Headcount progress over time. I found a video with a nice solution to that, but for some reason it does not work exactly how I want.

This is the formula:

Headcount = Calculate(COUNTROWS('[HR Actions] Actions in EC'), FILTER(values('[HR Actions] Actions in EC'[Hire Date]), '[HR Actions] Actions in EC'[Hire Date] <= MAX( Dates[Date])), filter(values('[HR Actions] Actions in EC'[Term Date]), OR('[HR Actions] Actions in EC'[Term Date] >= MIN(Dates[Date]), isblank('[HR Actions] Actions in EC'[Term Date] ) ) ) )

My issue is that instead of counting everyone filtered with Hire Date before or on a given Date, it only filters and counts people hired precisely on the given date, meaning the sign "<=" seems not to be working properly...

Any ideas what might be the problem??

[Payeras_BI · Accepted solution]

Hi Allie,

Make sure the relationship between these two dates ([Hire Date] & [Term Date]) and the [Date] from your Calendar Table is inactive.

Regards,

J. Payeras

[Allie]

Thank you 🙂

I was sure they were inactive, but went to check again and indeed relation to Hire Date was active for some reason...

[Anonymous]

Please, first learn about how to build correct models:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

Then, try to learn about relationships in DAX:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

Then, try to learn about time-intelligence in DAX:

https://docs.microsoft.com/en-us/learn/modules/dax-power-bi-time-intelligence/

Then you'll be well equipped with knowledge to face issues like the one above.

[themistoklis]

@Allie 

Can you tell us more about your data model?

You use both Hire Date and Term Date in your formula