Forum Discussion

PowerBIExplorer's avatar
PowerBIExplorer
Frequent Visitor
8 years ago
Solved

Excel to Power BI Dax

Hi, 

 

In column 'OK' - # of month is a calculated column in excel with the following formula:

=IF(OR(O4="",DAYS($C$1,O4)>365),13,IF(YEAR(O4)=YEAR($C$1),MONTH($C$1)-MONTH(O4),12+MONTH($C$1)-MONTH(O4)))

 

 

 

 

I'm not sure sure I can create recreate this formula in Power BI... I can't find the equalivence for days in dax....

 

 

Thanks.

Need Help

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    Hey,

     

    it's little bit difficult for totally understand your requirement from the screenshot and your Excel Formula. It would be really helpful if you created some sample data in a PBIX file, upload this file to onedrive or dropbox and share the link ;-)

     

    But nevertheless maybe this gets you started (I assume you want to create a calculated column):

    Your Excel O4 = "" can be translated to DAX like so ISBLANK('tablename'[ColumnameFromO4])

     

    Assuming that the data type of your column O4 is something like datetime or date than you can use the DAX formulas YEAR('tablename'[ColumnameFromO4]), MONTH('tablename'[ColumnameFromO4])

    You can use DATEDIFF (https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax) to calculate the duration between two dates for a certain unit like days

     

    Overall I would suggest to use the DAX formula SWITCH to avoid cluttered nested if statements like so:

    SWITCH(
    TRUE()
    ,your first condition, 13
    ,your 2nd condition, ...
        ,maybe a something else :-)
)

     Hopefully this gets you started

     

    Regards

    Tom