Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Convert an excel-formula to DAX

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

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.