Forum Discussion

jhowe1's avatar
jhowe1
Helper III
5 years ago
Solved

Average not in date period

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 W...
Need Help
  • jhowe1's avatar
    jhowe1
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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]))))