Calculate AVG 3M, based on Working Days

[y5famfnatudu]

Hello,

In Dax, I have this measure: 

_AVG 1Y Revenue v2 =
AVERAGEX (
    DATESINPERIOD ( Date[Date], LASTDATE ( Date[Date] ), -1, YEAR ),
    [Revenue]
)

 How can I filter Date[Date] so that the average is calculated based on the working days column Date[isWorkingDay] (0 for weekends, 1 for working days)? putting in mind that DATESINPERIOD doesn't accept virtual tables as its first parameter?

Best regards,

Simon

[johnt75 · Accepted solution]

Try

_AVG 1Y Revenue v2 =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR LastYear =
    DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), DAY ( MaxDate ) )
VAR DatesToUse =
    CALCULATETABLE (
        VALUES ( 'Date'[Date] ),
        'Date'[Date] >= LastYear
            && 'Date'[Date] < MaxDate
            && 'Date'[Is Working Day] = 1
    )
RETURN
    AVERAGEX ( DatesToUse, [Revenue] )

[y5famfnatudu]

Thank you so much @johnt75