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, av...
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.

  • 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.

       

       

      • Jensej's avatar
        Jensej
        Helper V
        6 years ago

        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