Cannot find table while creating a measure using a Table defined withing measure context

Hi marcoie,

Thank you for reaching out to the Microsoft Fabric Forum Community.
And also Thanks to Jihwan_Kim  for quick and helpful response.

try this dax measure:

NewDate =
VAR StartDate = [_plan_test_end_date]
VAR DaysToAdd = _more_days[_more_days_value]

VAR FilteredWorkingDaysTable =
FILTER(
dates_reference,
dates_reference[Date] >= StartDate &&
dates_reference[IsWorkingDay] = TRUE
)

VAR WorkingDaysTable =
ADDCOLUMNS(
FilteredWorkingDaysTable,
"DayOffset",
RANKX(
FilteredWorkingDaysTable,
dates_reference[Date],
,
ASC
) - 1 )

VAR TargetDate =
CALCULATE(
MIN(dates_reference[Date]),
FILTER(
WorkingDaysTable,
[DayOffset] = DaysToAdd
)
)

RETURN
TargetDate

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & regards,
Prasanna Kumar

Hello marcoie ,

This is probably due to use of EARLIER function with the same variable. This typically causes a context conflict because EARLIER expects a row context that isn't being properly established when you're referencing the same table variable inside the same scope.

Here is alternate code you can use :

NewDate =
VAR StartDate = [_plan_test_end_date]
VAR DaysToAdd = [_more_days_value]

VAR FilteredWorkingDaysTable =
FILTER(
dates_reference,
dates_reference[Date] >= StartDate && dates_reference[IsWorkingDay] = TRUE
)

VAR WorkingDaysTable =
ADDCOLUMNS(
FilteredWorkingDaysTable,
"DayOffset",
RANKX(
FilteredWorkingDaysTable,
dates_reference[Date],
,
ASC,
Dense
)
)

VAR TargetDate =
MINX(
FILTER(
WorkingDaysTable,
[DayOffset] >= DaysToAdd
),
dates_reference[Date]
)
RETURN
TargetDate

I hope this helps.

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Warm Regards,

Thanks this approach ALSo worked well.

Yet, what I did while you guys were patiently creating answers was to create a side "dates_table" that contains ONLY the working days (process is straight forward: I create the standard calendar table picking up my oldest data element date, and going 1 year into future, then I use a REST service that brings national holidays and define a IsWorkingDay based on weekday and national holiday flag, finally I filter that DateTable into a single column table with only the working dats dates (no time), and from there I create an Index, so I just have to "find" the start date, and then add or subtacrt the working days needed on same index to find new date.

I might need to "tests" which metod is faster or has less mem consumption: As I need to use this work days addition/subtraction only with measurements and not for columns, probably the dynamic filtering should be enough, but for a more permanent approach perhaps a dedicated working days table plus index/rank should be faster.