Commulative Sum With Slicers Dates

[tabuzahra]

Dears,

I'm not that experienced in Power Bi measures, though I'm seeking your help in the below scenario:

1. I have a calendar date table that includes each day the working dates hours
2. I have the resources table that includes the hiring and termination date for each employee
3. I have a slicer with the Calendar dates (Start & End)

What I'm looking for is to calculate the sum of working dates hours between the slicer dates. Bearing in mind:

1. If the slicer start date is >= hiring date then it should take the slicer start date, otherwise it will take the hiring date
2. If the slicer end date is >= termination date then it should take the termination date, otherwise it will take the slicer end date

Below I have this DAX query, though it returns the ALL the sum of working dates hours from slicer start and end dates without considering the termination date of the employees.

My DAX:

Resources Net Hours - ALL =
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' ) )
VAR Start_Date =
if(Slicer_Start_Date>=Hiring_Date,Slicer_Start_Date,Hiring_Date)
VAR End_Date=
if(Slicer_End_Date>=Termination_Date,Termination_Date,Slicer_End_Date)
RETURN
CALCULATE ( SUM ( 'Calendar'[Net Hours] ),
FILTER (
'Calendar',
'Calendar'[Date] >= Start_Date
&& Calendar[Date] <= End_Date
&& ISBLANK([OTL Hrs Sum])
|| [OTL Hrs Sum]=0
|| [OTL Hrs Sum]>0
) )

Sample Data:

Slicer Date let's say from 1 Jan 2020 - 30 Apr 2020 and that each working date net hours is 8 hours a day

Employee Name	Hiring Date	Termination Date	Actual Net hours between slicers	Resources Net Hours (Above DAX)
X	1 Dec 2017	2 Mar 2020	352	686
Y	5 Feb 2020	30 Apr 2020	486	686

[v-kelly-msft]

Hi @tabuzahra ，

Create 2 measures as below:

Actual Net hours between slicers = 
var _min=MINX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _max=MAXX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _minactual=IF(_min>=MAX('Table'[Hiring Date]),_min,MAX('Table'[Hiring Date]))
var _maxactual=IF(_max>=MAX('Table'[Termination Date]),MAX('Table'[Termination Date]),_max)
Return
CALCULATE(SUMX(FILTER(ALL('Table 2'),'Table 2'[Date]>=_minactual&&'Table 2'[Date]<=_maxactual),'Table 2'[Column])*8,ALLEXCEPT('Table','Table'[Employee Name]))

Resources Net Hours (Above DAX) = 
var _mindate= MINX(FILTER(ALL('Table 2'),'Table 2'[Date]>=DATE(2020,1,1)&&'Table 2'[Date]<=DATE(2020,4,30)&&'Table 2'[Column]=1),'Table 2'[Date])
var _maxdate=MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]>=DATE(2020,1,1)&&'Table 2'[Date]<=DATE(2020,4,30)&&'Table 2'[Column]=1),'Table 2'[Date])
Return
SUMX(FILTER(ALL('Table 2'),'Table 2'[Date]>=_mindate&&'Table 2'[Date]<=_maxdate),'Table 2'[Column])*8

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[tabuzahra]

Hi Kelly,

Much appreciated, it works fine.

Though, how do I show 0 instead of blank in case the slicer start date is > of the hiring date? 

[v-kelly-msft]

Hi @tabuzahra ,

Sorry I didnt quite catch your meaning...in the output ,it's a total value of hours,so where do you mean about the blank?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[tabuzahra]

Hi @v-kelly-msft ,

What I meant that when you choose the slicer start date > termination date, it gives blank in the "Actual net hours between slicers" where I want it to give a value 0. As I'm using this measure to calculate something else with it in another measure.

[v-kelly-msft]

Hi @tabuzahra ,

But your slicer is a date period,which one would you wanna compare with termination data?Or do you mean once any date in the date period is after termination date,then it should return 0??

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

This would be far easier to troubleshoot with sample source data or the PBIX. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

If you have employee name in a visual, then you should be able to do something like this:

Resources Net Hours - ALL =
var Current_Employee = MAX('HR Resources'[Employee Name])
var Hiring_Date = MAX ( 'HR Resources'[Enterprise Hire Date] ) // context already filters to employee
var Termination_Date = MAX ( 'HR Resources'[Termination Date])
VAR Slicer_Start_Date = MIN ( 'Calendar'[Date] ) // again, context already filters (slicer)
VAR Slicer_End_Date = MAX ( 'Calendar'[Date] )
VAR Start_Date =
if(Slicer_Start_Date>=Hiring_Date,Slicer_Start_Date,Hiring_Date)
VAR End_Date=
if(Slicer_End_Date>=Termination_Date,Termination_Date,Slicer_End_Date)
RETURN
SUMX (
  FILTER (
    'Calendar',
      ('Calendar'[Date] >= Start_Date
      && Calendar[Date] <= End_Date )
      && ( ISBLANK([OTL Hrs Sum])
      || [OTL Hrs Sum]=0
      || [OTL Hrs Sum]>0 )
  ),
  'Calendar'[Net Hours]
)

There are a lot of places that things could be going wrong. I am thinking your filters at the end maybe so I tried to fix those.

[tabuzahra]

Dear Greg,

Thanks a lot for your quick response. Actually it did work fine, though I'm facing this below issue:

If I choose the dates in the slicer from 1 Jan 2020 until 30 Apr 2020, It returns the correct working dates hours for employee Y (his termination date was 2 Mar 2020) which is 352 instead of 686. Though, If I choose the slicer dates from 15 Jan 2020 until 30 Apr 2020, it returns BLANK.

Hint: The employees table has a relationship with the TimeSheet table by person number.

So after I have investigated it, the dax that have been provided from your kind self returns the correct working dates hours only if the employee has submitted hours in the Timesheet table between the slicers dates and the employee didn't submit any hours between the slicer dates it returns BLANK.

I'm not sure why it looks for the values in the timesheet table.