Measure Showing Blank When Paired With Date

[Anonymous]

Hi All,

I have the following measure:

Annual Total Working Days Lost Count =

VAR DayOfWeek = ADDCOLUMNS('Date', "Weekday", WEEKDAY([Date],2))

VAR _FILTER = FILTER(DayOfWeek, [Date] >= MAX('Prototype Annual Data'[Start Date of Absence]) && [Date]<= IF( MAX('Prototype Annual Data'[End Date of Absence])=BLANK(),TODAY(), MAX('Prototype Annual Data'[End Date of Absence])))

RETURN

CALCULATE(COUNTX(FILTER(_FILTER, NOT([Weekday] IN {6,7})),[Date]), USERELATIONSHIP('Date'[Date], 'Prototype Annual Data'[Start Date of Absence]))

which counts the total working days lost for absences regardless of whether they are ongoing or closed. The measure works correctly when paired with the Start and End Date of Absences from the 'Prototype Annual Data' table. However, appears blank when paired with the likes of Month and Year from my Date:

My Date table is connected to the Prototype Annual Data Table via two inactive relationships:

I thought that this may be the result of the problem and introduced a USERELATIONSHIP filter but this does not seem to change the outcome.

I do not actually need the measure to work in this may but rather to validate that another measure (the sum of this count) is working properly and the correct absence days are being assigned to the correct months.

Many thanks!

[lbendlin]

when designing measures use variables and CONCATENATEX to validate the results of each intermediate step. That will help you figure out where your context transition is messing up the result.