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(...
Need Help
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

 

PowerBIExplorer's avatar
PowerBIExplorer
Frequent Visitor
8 years ago

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.