Convert an excel-formula to DAX

[Anonymous]

Hi,
Can someone help me with converting the following Excel-formula to Dax?

=IF(ISBLANK(F2);IF(YEAR(E2)=2021;(-MONTH(E2)+12)/12;1);MONTH(F2)/12)

Any help is greatly appreciated!

Regards,
sondi

[v-chenwuz-msft]

Hi @Anonymous ,

@amitchandak  is right if you want to create columns in power bi. I make some additions. If you want create measure, the parameter of ISBLANK() can not be [field name] directly. Use selectedvalue( 'table'[fied name] ) or max( 'table'[fied name] ) replace "F2", so as function MONTH(),YEAR(). Selectedvalue() means return current row's value.

For example, i have one table named test.

ID	start date	end date
001	2/1/2022	2/15/2022
002	2/2/2022
003	2/3/2022	2/16/2022

And measure

Measure =
IF(
    ISBLANK( SELECTEDVALUE( 'Table'[end date] ) ),
    IF(
        YEAR( SELECTEDVALUE( 'Table'[start date] ) ),
        ( - MONTH( SELECTEDVALUE( 'Table'[start date] ) + 12 ) ),
        1
    ),
    MONTH( SELECTEDVALUE( 'Table'[end date] ) ) / 12
)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , This should work with column name in power bi

F(ISBLANK([F]);IF(YEAR([E])=2021;(-MONTH([E])+12)/12;1);MONTH([F])/12)

E and F should be date