Excel to Power BI Dax

[PowerBIExplorer]

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.

[TomMartens]

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

[PowerBIExplorer]

Thank you, here is a sample dataset of what I'm working with. 

https://app.box.com/s/ckaqvj9maiy5gt6jdsqy8h51mwgkr3uq

Columns J and K are the columns I need to replicate in Power BI as calculated columns.

I'm not sure to input the data for "Measures as of'' into Power bi since it's not a whole column. Maybe I can replace the cell in the formula with the actual dates, but I find that tedious if I need to update it everytime I update the excel sheet. 

[v-yuezhe-msft · Accepted solution]

@PowerBIExplorer,

Please review the DAX in the PBIX file below.

https://1drv.ms/u/s!AhsotbnGu1Nok0Ck5EKND2-5SLIk

Regards,
Lydia