Average not in date period

[jhowe1]

What is the equivalent DAX of the following SQL? i.e. vends not in date period...

SELECT AVG(CountRows)
FROM pbi.FactVend AS FV
    JOIN pbi.DimAsset AS DA ON DA.KEY_Asset = FV.KEY_Asset
WHERE CAST(FV.KEY_VendDate AS Date) NOT BETWEEN DA.ExcludedFromDate AND DA.ExcludedToDate

Something along the lines of (can't get DAX right)

Average Cup Vends = 
CALCULATE(AVERAGE(Vend[CountRows]), CONVERT(Vend[KEY_VendDate], DATETIME) 
NOT IN FILTER(Asset, DATESBETWEEN(CONVERT(Vend[KEY_VendDate], DATETIME), Asset[Excluded From Date], Asset[Excluded To Date])))

[v-jayw-msft]

Hi @jhowe1 ,

Please show some sample data and expected result.

Best Regards,

Jay

[jhowe1 · Accepted solution]

The answer to this was simply to add the keyword

RELATED ( Asset[Excluded From Date] ),

RELATED ( Asset[Excluded To Date] ) to my fields from the non filtered table.

[amitchandak]

@jhowe1 , Try a measure like

Average Cup Vends =
CALCULATE(AVERAGE(Vend[CountRows]), FILTER(Asset, not(DATESBETWEEN(CONVERT(Vend[KEY_VendDate], DATETIME), Asset[Excluded From Date], Asset[Excluded To Date]))))

[jhowe1]

Thanks but this won't work, the rows i want to filter are in 'Vend', but the dates i want to filter by are in Asset (two different tables). It also doesn't seem to like converting my KEY_VendDate into a datetime this is in format int YYYYMMDD.

There is a relationship between these two tables 

[jhowe1]

Can someone help me finish this please? I simply need to filter the vends where the venddate is not in the asset table period excluded dates Asset[Excluded From Date], Asset[Excluded To Date]...