count rows containing blank and date values for a given month

[Anonymous]

Hi there,

I am trying to build an HR report showing employee counts per month that I can compare  to a forecast.

I have a (calculated) column containing end of contract dates and blanks. One row represents a unique employee. It seems logical to me that I should do something like this:

Total rows – (rows containing date < Month) = employees at a given Month.

I’m quite new to PowerBi and my Dax knowledge is limited. So far I’ve been able to get a table showing the count of employees with an end of contract date per given month using this measure:

measure=
if(not(isblank(max(Datetable[Datum]))),

CALCULATE(distinctcount(Table1[rows.employee]),

USERELATIONSHIP(Table1[enddate_calculated],

Datetable[Datum])),

blank())

However, I can’t find a way to use this to subtract it from the total row count in a way that I can visualize it per month.

Any suggestions on how to get the desired result?

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below to get the count of employees:

Employee Count =
VAR __DATE =
    MAX ( 'datetable'[Date] )
RETURN
    SUMX (
        Table1,
        IF (
            Table1[DateStarted] <= __DATE
                && OR (
                    Table1[enddate_calculated] >= __DATE,
                    ISBLANK ( Table1[enddate_calculated] )
                ),
            1,
            BLANK ()
        )
    )

And you can refer the following links to get it:

How Many Staff Do We Currently Have – Multiple Dates Logic In Power BI Using DAX

---

Create inactive relationships
Create measureCreate visuals

---

Count Amount of Active Employees by period

Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell

Best Regards

[Anonymous]

Hi @amitchandak,
That looks great! I will try it out, as it exeeds my original requirements.

Meanwhile another working solution was offerd to me (by a colleague) in the form of this formula:

measure =
VAR selectedDate= MAX(datetable[Date])

RETURN

SUMX(Table1,

VAR INDIENSTDATUM = Table1[rows.Start_contract]

VAR UITDIENSTDATUM =Gegevens_Externe_Medewerkers[rows.End_contract_set]

RETURN IF(INDIENSTDATUM<= selectedDate && OR(UITDIENSTDATUM>=selectedDate, UITDIENSTDATUM=blank()

),1,0)

)

[amitchandak]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.