Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure: Retrieve value based on date slicer

Hi,

I need your help with a Measure that will retrive the count of employees based on date slicers: Year and/or Month

I have an employee table with two date columns: Start Date and End Date, as well as one column with status Aktiv/Inactiv.
The slicers are based on a calender table set with an active relation ship for Start Date and an inactive on End Date.

The formular should look something like follows:

If nothing is filtered then return count of employees with status aktiv
Otherwise if filtered return count of empoyees where:
MAX Date >= Start Date  OR Start Date is empy
AND
MAX Date < End Date OR End Date is empty

Thanks for your help!

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Refer to my blog on this :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Proud to be a Datanaut Connect on Linkedin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks AC, this is a helpful start. Question:

      In your formular: 

      Last Period Employee =

       between the two VAR there is no komma:

      var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',DATEADD('Date'[Date],-1,MONTH)),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))

      If I write the mearue it shows me an error; however, if I do not put a komma between the two VAR, I get an error with after entereing RETURN value:

      Return
 CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

      Is there suppose to be a comma between the VAR?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Nevermind, I found the eror in my formula setup. Formular is working now, unfortuantely it gives me the incorrect numbers. I will have to see why.