Define a value depending on a range of dates

[Anonymous]

Dear all,

I´m struggling with a case that I´ll try to show on the example below.

I have two different tables:

Table: SAP_HR-Z_PA2001 - shows each vacation period that an employee has taken. Example of one employee:

User ID	Beginning of Vacations	End of Vacations
7447	21/12/2015	30/12/2015
7447	10/02/2016	29/02/2016
7447	01/03/2017	30/03/2017
7447	05/02/2018	06/03/2018

Table: SAP_HR-Z_PA2006 - shows the range of dates when the employee has the right to take the leave (in Brazil, for every year of work, the employee has the right (and obligation) to take 30 days of absence in the following year). Example of the same Employee:

User ID	Initial Period	Final Period	Date Limit
7447	10/03/2014	09/03/2015	09/03/2016
7447	10/03/2015	09/03/2016	09/03/2017
7447	10/03/2016	09/03/2017	09/03/2018

This means that, looking at the first line, from March 9th 2015 to March 9th 2016 has to complete 30 days of vacations, that can be split within 3 different periods.

So, what I´m trying to achieve is the tell, on the first table, which of the corresponding period the employee has taken its absence:

User ID	Beginning of Vacations	End of Vacations	Vacation Period
7447	21/12/2015	30/12/2015	2014/2015
7447	10/02/2016	29/02/2016	2014/2015
7447	01/03/2017	30/03/2017	2015/2016
7447	05/02/2018	06/03/2018	2016/2017

If they were in the same table, I would do something like

IF ([End of Vacations] > [Final Period] && [End of Vacations] < [Date Limit]

  THEN YEAR([Initial Period]) & "/" & YEAR(Final Period])

)

Thank you for any help.

Eduardo

[Anonymous]

I´m trying this formula, but it´s returning me "DAX comparison operations do not support comaring values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

Vacation Period = 
var currentEmployee = 'SAP_HR-Z_PA2001'[User ID]
var vacationEnd = 'SAP_HR-Z_PA2001'[End of Vacations]
var vacationPeriod = CALCULATE(
			FIRSTNONBLANK('SAP_HR-Z_PA2006'[Final Period];1)
			;FILTER('SAP_HR-Z_PA2006';'SAP_HR-Z_PA2006'[User ID] = currentEmployee 
			     && 'SAP_HR-Z_PA2006'[Final Period] <= vacationEnd 
			     && 'SAP_HR-Z_PA2006'[Date Limit] >= vacationEnd
			     )) 
return
            IF(vacationPeriod = "";"";vacationPeriod)

[v-chuncz-msft]

@Anonymous,

You may use ISBLANK Function and BLANK Function instead of the empty string.

[Anonymous]

Thank you. It solved me the error type, but now gives me "A circular dependency was deteced: SAP_HR-Z_PA2001[Vacation Period], 1bda6a34-e63f-[...], SAP_HR-Z_PA2001[Vacation Period]

---

wrote:

@Anonymous,

You may use ISBLANK Function and BLANK Function instead of the empty string.

---