Forum Discussion
Comulative Sum Between Two Slicer Dates
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.
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?
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.
Hello,
Kindly, below you can access the sample data.
Explaination:
- Calendar Table
- 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)
- 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)
What I need is:
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") . 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.
Please let me know if you need any further clarifications