Measure formula in Power BI Desktop

[Barto]

Hi,

Hope this is the right forum. I have a database of employees each with a start date and in the termination date column it populate with a termination data if they have resigned but it blank if there are still active.

I'm trying to report on the active headcount but date (date X) so the formula needs to pick of if an employee "was" active on a specific date (date x)

I need a measure formula in Power BI Desktop that is "countifs" type of formula

The formula needs to count if meeting 3 criteria

StartDate is Greater than or equal to date X
TerminationDate is less than or equal to date x OR is blank

hope this makes sense

 

[cyrilbrd]

Hope someone will confirm this but I think you should play with =CALCULATE() as it is and I quote Rob Collie "A Supercharged SUMIF()"

 

[Barto]

Hope someone will confirm this but I think you should play with =CALCULATE() as it is and I quote Rob Collie "A Supercharged SUMIF()"

agree it will be some sort of calculate dax formula but I'm a real novice with dax hence why I have reached out for help.

 

[Ozeroth]

I think an 'events in progress' pattern would apply.

It is discussed in this paper with some DAX query examples near the end of the paper
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

and on Chris Webb's blog
A New Events-In-Progress DAX Pattern | Chris Webb's BI Blog

 

[scottsen]

Oz is totally right. Also see Two methods to calculate Person-Hours | Power Pivot | Tiny Lizard