Transferring DAX formulas from Powerbi to Power Pivot in Excel

[milkywaypowerbi]

Hi, 

I currently have this set of codes in Powerbi which is functional. However, I would like to transfer the DAX codes to Excel as a measure for Power Pivot. However, the syntax error kept appearing. Can someone pls assist me with the syntax issue on Excel.

NPV Test 001 =
VAR _selfamily =
if(HASONEVALUE( Family[Family]),VALUES(Family[Family]),0)
VAR _rate =
SWITCH ( _selfamily, "Book", 0.10, "Pen", 0.08 )
VAR _selyear =
if(HASONEVALUE(Year[Year]),VALUES(Year[Year]),0)
VAR _diffyear =
_selyear - SWITCH ( _selfamily, "Book", 2020, "Pen", 2019 )
RETURN
IF (
ISBLANK ( _diffyear )
|| _diffyear < 0,
BLANK (),
DIVIDE ( 1, POWER ( 1 + _rate, _diffyear ), 0 ) * Operating Profit)

[m3tr01d]

@milkywaypowerbi Hmm, I don't see anything that looks an unsupported DAX expressions in Power Pivot.

I might be wrong but I think Power Pivot needs to have ":=" instead of "=" when declaring a measure?

[milkywaypowerbi]

Hi @m3tr01d 

Thank you so much! That was the issue.

I am facing a new error as the operating profit has negative values for some year so there's an error showing : An arguement of function "POWER" has the wrong data type or the result is too large or small.

How can I modify this code for it to run for negative operating profit numbers.

Thank you!!