Workday Adjusted Date Logic

Hi  Anonymous ,

I created some data:

Table:

holiday table:

Here are the steps you can follow：

1. Create calculated column.

Test =
var _week=WEEKDAY('Table'[Date],2)
var _column=SELECTCOLUMNS('holiday table',"date",[Date])
return
IF(
  'Table'[Date] in _column ||   _week in {6,7},0,1)

Workday =
IF(
    [Test]=0,BLANK(),
RANKX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&[Test]=1),'Table'[Date],,ASC)
)

Current =
IF(
    YEAR('Table'[Date])= YEAR(TODAY()),
    "Currrent Year")

2. Create measure.

compare =
var _lastyear=
MAXX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))-1&&'Table'[Workday]=MAX('Table'[Workday])),[Date])
var _value=
SUMX(
    FILTER(ALL('Table'),'Table'[Date]=_lastyear),[Value])
return
IF(
    MAX('Table'[Test])=BLANK(),BLANK(),
MAX('Table'[Value])- _value)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly