Forum Discussion

tabuzahra's avatar
tabuzahra
Icon for Helper II rankHelper II
6 years ago

Showing Comulative Sum According To Slicer Dates

Hello,

I'm seeking your support in this impressive community in the following challenge that I'm facing,

 

I have created a measure to calculate the standard hours for each employee accoring to the dates selected in a slicer, but my challenge is

  1. The total standard hours for each employee is not correct as it should calculates the standard hours for each employee between the Hire date and Termination date. 
    1. The employee standard hours = (the correspondent standard hours for each date of the employee "working dates") - (the correspondendt standard hours for each date of the employee "Vacation Dates") .
    2. Taking into consideration that there is a date slicer and it should preview only the standard hours (Working Dates & Vacation Dates) between the specified filteres slicer dates.
  2. That the standard appears (BLANK) when I filter the slice between two dates.

 

below you can access the sample data. Sample Data

 

Explaination:

  1. Calendar Table
  2. HR Resources: It has all the hire & termination dates and I have expanded the dates between the hire & termination in the column (Working Dates) and (There is relationship between it and the Calendar table)
  3. HR Leaves: It has all the employees vacation and it contains the start and end dates for all the vacation periods. I have expanded the dates between the leave start and leave end dates in the column (Vacation Dates) and (There is a relationship between it and the HR Resources (Employee Name)). Also there is a inactive relationship between it and the calendar table (Vacation Dates & Calendar Date)

 

 

My measure that I have created is:

var Current_Employee=
MAX('HR Resources'[Employee Name])
var Hiring_Date =
CALCULATE (
MAX ( 'HR Resources'[Enterprise Hire Date] ),
FILTER ( 'HR Resources', 'HR Resources'[Employee Name] = Current_Employee ) )
var Termination_Date =
CALCULATE (
MAX ( 'HR Resources'[Termination Date]),
FILTER ( 'HR Resources', 'HR Resources'[Employee Name] = Current_Employee ) )
VAR Slicer_Start_Date =
CALCULATE (
MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR Slicer_End_Date =
CALCULATE (
MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
RETURN
CALCULATE ( SUM ( 'Calendar'[Net Hours] ),
FILTER (
'Calendar',
'Calendar'[Date] >= Hiring_Date
&& Calendar[Date] <= Termination_Date
&& Calendar[Date] >= Slicer_Start_Date
&& Calendar[Date] <= Slicer_End_Date ) )
 
 
Need Help

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi tabuzahra,

    You want to calculate cumulative net hours for the still working employees according to slicer dates . Right? The hours are between Termination Date and Enterprise Hire Date, and except vacation dates, weekends, holidays and Ramadan. Do I understand correctly?

    Or we just need to calculate the hours between Termination Date and Enterprise Hire Date and expect vacation dates?

     

    Best Regards,

    Xue Ding

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

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi tabuzahra ,

    Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

     

    Best Regards,

    Xue Ding

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