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

[marcoie]

In bellow image, dates_reference is my global dates table. This code snipped allos to add a number of working (non weekend, non holiday) days to any given date (here initial dates is  [_plan_test_end_date] and days to add is the output of a variable segmentation tied to a manual table _more_days[_more_days_value]).

Steps are:
1. FIlter general dates table (which is big) for the time starting from the date u wanna work with, excluding non working days.
2. Calculate how many days (rows) are beween a row and all previous rows as an offset.

3. See for which date, the offset match teh working days needed.

Crude, but works in tehory. Yet, I define all the "filtered" date tables within the measure, FilteredWorkingDaysTable seems to be created properly (no error), but is not visible in the context of second WorkingDaysTable, seems is not "visible" for using its atributes as filter condition.

Copilot did not helped.

Any clue?

[v-pgoloju · Accepted solution]

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

[divyed · Accepted solution]

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,

[divyed · Accepted solution]

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,

[marcoie]

Thanks!!! RankX solved the reference issue

[v-pgoloju · Accepted solution]

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

[marcoie · Accepted solution]

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.

[Jihwan_Kim]

Hi,

I am not sure how the semantic model looks like, but please try something like below and check if it works.

In the above measure,

Line14 -> change filteredworkingDaysTable[Date] to  date_reference[Date]

Line 21 -> change workingDaysTable[Dayoffset] to [Dayoffset]

Line 23 -> change workingDaysTable[Date] to date_reference[Date]