Forum Discussion

tabuzahra's avatar
tabuzahra
Helper II
6 years ago

Comulative Sum Between Two Slicer Dates

Hello Everyone,

I rarely ask for help in the community as I found all my answers already answered, but I really need your support in my below callange.

 

Kindly, I have a detailed calendar table where it has (date, weekends, standard hour per day, etc.) and I have a table of the employees Data, where it has the Hiring Date and the Termination date for each resource.

 

What I need is to create a dax to sum the comulative standard hours for each employee according to the hiring and termination dates, but the value should be filtered by the selected dates on the slicer.

 

Example:

  • The slicer selected date is from 1 Jan 2020 - 30th of Jan 2020
  • Let's say the employee (X) his hiring date was on 5th of Jan 2020 and his termination date is 20th of Jan 2020.
  • The comulative sum of his standard hours should only show from the hiring date until the termination date.

 

If you need any further clarifications please let me know and I would appreciate your support.

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Refer if this blog can help you

    HR-Analytics-Active-Employee-Hire-and-Termination-trend

     

    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 Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    tabuzahra ,

     

    Soppose the slicer is based on a third "SlicerTable", you may create a measure in Employee table using dax like pattern below:

    Total Hours =
VAR Current_Employee =
    MAX ( Employee[Name] )
VAR Hiring_Date =
    CALCULATE (
        MAX ( Employee[Hiring Date] ),
        FILTER ( Employee, Employee[Name] = Current_Employee )
    )
VAR Termination_Date =
    CALCULATE (
        MAX ( Employee[Termination Date] ),
        FILTER ( Employee, Employee[Name] = Current_Employee )
    )
VAR Slicer_Start_Date =
    CALCULATE ( MIN ( SlicerTable[Date] ), ALLSELECTED ( SlicerTable ) )
VAR Slicer_End_Date =
    CALCULATE ( MAX ( SlicerTable[Date] ), ALLSELECTED ( SlicerTable ) )
RETURN
    CALCULATE (
        SUM ( Calendar[standard hour per day] ),
        FILTER (
            Calendar,
            Calendar[Date] >= Hiring_Date
                && Calendar[Date] <= Termination_Date
                && Calendar[Date] >= Slicer_Start_Date
                && Calendar[Date] <= Slicer_End_Date
        )
    )

    Community Support Team _ Jimmy Tao

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

     

    • tabuzahra's avatar
      tabuzahra
      Helper II
      6 years ago

      Hello,

      It's working with the Dax that you have pasted, but the issue is when I use the slicer date to certain dates, some of the employee values disappear.

       

      what would be the reason?

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        6 years ago

        tabuzahra ,

         

        I could not reproduce your issue. Could you please show some sample data? And clarify more details about this issue?

         

        Community Support Team _ Jimmy Tao

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