Checking Employee Count on specific date.

[Jensej]

Hello There! 

So im pretty new to Power BI. 

Our company has some Groups with assisted living. (5 floors/groups) 

I have made a report where i can see Total amount of residents,gender, avg age and where i can filter thruu the groups. 

Everything works perfect until my boss asked me to go back in time and see how many residents we had at a specific date.  

If i add a date slicer my Values isnt correct. 

My goal is to have a single Date field in the report where my boss can add i date and the report would respond on that input. 

Here is how the report looks like:

In the modeling i also have an Active Link from date of Entry and an inactive Link from date of Exit. 

Can someone help me? 

[amitchandak]

@Jensej , Can you share sample data and sample output in a table format?

check if that can help : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Jensej]

Hi @amitchandak 

Not really what i was looking for. I wanted like a Date slicer with only one input field on the report. 

And if i put for example 1.1.2020 i want to see how many People was living on that floor at the moment. 

It has to calculate like this:

Entry Date <= 1.1.2020 

Exit Date > 1.1.2020 or Empty 

Now i solved it with two page filters one for Entry Date and one for Exit Date but that could be to complicated for the Enduser that's why i wanted the Field directly in Report

[v-yingjl]

Hi @Jensej ,

Try like this measure and put it in a card visual:

Measure =
VAR _date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _people =
    CALCULATE (
        SUM ( 'Table'[count] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] <= _date )
    )
RETURN
    IF (
        _date = BLANK (),
        "Please select a date in the slicer",
        "The number of people as of the selcected date is: " & _people
    )

Attached a sample file that hopes to help you: Checking Employee Count on specific date.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Jensej]

Hi @v-yingjl and @amitchandak  

Thanks for your replies

So i think im pretty close to a solution now. 

I read in another artictle that the easiest way to have only one date to fill in is to only activate the after option in the slicer. Like this i don't have a long list with dates. In my measure i therefor have to change SELECTEDVALUE to MIN.  

I have this Measure but i still don't get the correct Value. 

I think the problem is at the Var _Employees...  I want to say where " Entry_Date is before or same Date as the VAR _Date and the Resign_Date is Empty OR after the VAR _Date. 

Is it correct that && = AND and ||= OR in Dax?

Employees at specific Date =

Var _Date = MIN(bi_datum[datum])

Var _Employees= CALCULATE(COUNT(bi_Employee[id]),

FILTER ( ALL ( bi_Employee), bi_Employee[Entry_Date] <= _Date &&

(bi_Employee[Resign_Date] = BLANK() || bi_Employee[Resign_Date] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Please choose a Date", "Count of Employees at selected date is: " & _Employees

[v-yingjl]

Hi @Jensej ,

There seems to be no problem only from your formula. In dax, && = AND and || = OR are corrent so the logic has no problem.

Perhaps need more information about this for further discussion.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Jensej]

It works now, it was a problem with the Datamodeling. 

I can now choose a date in the Slicer and my measure shows me the Total Residents at that day. 

Now to the next problem 😞 

As showned in the Picture i want to Add a Pie Chart that shows me the Gender from the 46 Residents at that day. 

If i put Geschlecht (Gender in German) on the Legend and the Measure Anzahl Bew (Total Residents) as Values i get 46 everyone. It already summed up. I want to see that it was 20 Females and 26 Males at that day. How can i solve this? 

If i put the for example the Employee ID in Values instead of my new Measures it splits after Gender but all the Amount of Residents are showned and not the 46 at that specific date. 

[v-yingjl]

Hi @Jensej ,

You can modify your variable _empoyees like this:

var _employees = 
CALCULATE(
    COUNT('bi_employee'[id]),
    FILTER(
        ALLEXCEPT(bi_employee,bi_employee[Gender]),
        'bi_employee'[Entry_Date] <= _date &&
        ('bi_employee'[Resign_Date] = BLANK() || 'bi_employee'[Resign_Date] > _date)
    )
)

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Jensej]

@v-yingjl Thanks, your my hero! 

Im now able to choose one Specific Date and all the Calculations are correct. 

I have a second slicer on my Report where i should be able to filter on the different groups or combine some of them. 

For example i should be able to select Gruppe A and Gruppe B and the numbers will change. After i made all the changes with the specific date it doesn't work anymore. Do i have to make some measure from the Gruppe field and use this instead of the Original in the slicer to make it work? 

[v-yingjl]

Hi @Jensej ,

Continue to add the Group field in the filter which is similar with the previous formula:

var _employees = 
CALCULATE(
    COUNT('bi_employee'[id]),
    FILTER(
        ALLEXCEPT(bi_employee,bi_employee[Gender],bi_employee[Group]),
        'bi_employee'[Entry_Date] <= _date &&
        ('bi_employee'[Resign_Date] = BLANK() || 'bi_employee'[Resign_Date] > _date)
    )
)

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Jensej]

I don't really understand what you mean, create a new Measure or update my old one?

I have 3 different measures atm because 2 visuals is with a Count (Pie Chart, Anzahl Bewohner) and 1 is a AVG (Durschnitt Alter) and 1 is SUM (BESA Punkte). 

For the Pie Chart and Anzahl Bewohner i have this one:

Anzahl Bewohner = 

Var _Date = MIN(bi_datum[datum])

Var _Bew = CALCULATE(COUNT('bi_bewohner'[AdressNrADR]),

FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Geschlecht]),'bi_bewohner'[Wohnen Eintritt] <= _Date &&

('bi_bewohner'[Wohnen Austritt] = BLANK() || 'bi_bewohner'[Wohnen Austritt] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Bitte Datum auswählen", _Bew)

For the Durschnitt Alter

Durchschnitt Alter = 

Var _Date = MIN(bi_datum[datum])

Var _Bew = CALCULATE(AVERAGE('bi_bewohner'[Alter in Jahren]),

FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Alter in Jahren]),'bi_bewohner'[Wohnen Eintritt] <= _Date &&

('bi_bewohner'[Wohnen Austritt] = BLANK() || 'bi_bewohner'[Wohnen Austritt] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Bitte Datum auswählen", _Bew)

For the BESA Punkte

BESA Punkte = 

Var _Date = MIN(bi_datum[datum])

Var _Bew = CALCULATE(SUM('bi_bewohner'[Punkte BESA]),

FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Punkte BESA]),'bi_bewohner'[Wohnen Eintritt] <= _Date &&

('bi_bewohner'[Wohnen Austritt] = BLANK() || 'bi_bewohner'[Wohnen Austritt] > _Date) ) ) 

RETURN IF ( _Date = BLANK(), "Bitte Datum auswählen", _Bew)

[v-yingjl · Accepted solution]

Hi @Jensej ,

Just update your old measure, not write a new measure, add the group field in the below filter, refer to the previous formula in variable:

filter(allexcept(bi_employee,bi_employee(gender),bi_employee(group)),...)

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Jensej]

Hi @v-yingjl 

Perfekt now it works. I added this part on all 3 measures

FILTER ( ALLEXCEPT(bi_bewohner, bi_bewohner[Geschlecht],bi_bewohner[Gruppe],bi_bewohner[Alter in Jahren],bi_bewohner[Punkte BESA]),

Don't know if it necessary on all 3 measures but it works 🙂 Thanks