Forum Discussion

Jensej's avatar
Jensej
Helper V
6 years ago
Solved

Checking Employee Count on specific date.

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? 

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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.

12 Replies

    • Jensej's avatar
      Jensej
      Helper V
      6 years ago

      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's avatar
        v-yingjl
        Community Support
        6 years ago

        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.