Return 10 days Ago from Yesterday where [Is Business Day] = 1

[Adam01]

Hi All!

I have a query that requires me to return the date 10 days ago from yesterday where it only counts Business Days and skips weekends, bank holidays etc.

For Example, yesterdays date was "03/11/2022", I would need the measure to calculate the previous 10 days before this where it is ONLY a business day, so in theory this should return "21/10/2022" as this was 10 Business Days ago, however the below DAX returns "24/10/2022" so this is clearly counting Non Business Days.

VAR LastDateExpr = LASTDATE ( 'Calendar'[Date] )          <- Always set to be Yesterdays Date

VAR CalcExpr =

CALCULATE (

    DATEADD ( LastDateExpr , -10, DAY ),

       'Calendar'[IsBusinessDay] = 1

       )

RETURN

    CalcExpr

I am unsure where to go from here

[Greg_Deckler · Accepted solution]

@Adam01 Maybe:

Measure = 
  VAR LastDateExpr = LASTDATE ( 'Calendar'[Date] )
  VAR __Table = FILTER('Calendar',[Date] <= LastDateExpr && [Date] >= LastDateExpr - 14 && WEEKDAY([Date],2) < 6)
  VAR __Table1 = ADDCOLUMMNS(__Table, "__DaysAgo", COUNTROWS(FILTER(__Table,[Date] >= EARLIER([Date]))))
  VAR CalcExpr = MAXX(FILTER(__Table1, [__DaysAgo] = 10),[Date])
RETURN
    CalcExpr

[Adam01]

@Greg_Deckler Thank you! This works for what I need!