DAX Measure works in Power BI, but not Excel Power Pivot

Copper Contributor

I have the following measure used in Power BI to count the number of client interactions within 12 months by the other consultants from a selected consultant's sector practice.


I'm trying to use it in an identical Excel Power Pivot model (since this is the format I need to use to share with some work colleagues), but it seems I am stuck on an older version before operators such as SELECTEDVALUE and IN were introduced. I tried swapping in HASONEVALUE but end up with an error "Cannot find table 'Last12Months'. Any idea how to adapt the measure to work with Excel 2016 Version 16.x?  Thank you. 


Peer Interactions =
VAR SelectedConsultant = SELECTEDVALUE(Consultants[Consultant])
VAR SelectedSector = SELECTEDVALUE(Consultants[Sector])
VAR Last12Months = DATESBETWEEN(Dates[Date], TODAY()-365, TODAY())
Consultants[Sector] = SelectedSector &&
Consultants[Consultant] <> SelectedConsultant
Records[Consultant] <> SelectedConsultant,
Records[Activity Date] IN Last12Months

1 Reply
Does VAR work in a simple formula in Excel 2016?