Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
kmuir
New Member

Calculated column based on date fields

I have a requirement to create a calculated measure either in desktop in report or in the query. How can we achieve this using if then, also at report only measure can be used to create a measure it doesnot allow me to use date columns:

 

(CASE WHEN (FACT_ORDER.ORDER_ACTUAL_END_DATE > FACT_ORDER.ORDER_SCHED_END_DATE) THEN (FACT_ORDER.ORDER_ACTUAL_END_DATE - FACT_ORDER.ORDER_SCHED_END_DATE) ELSE 0 END)

 

Any help is appreciated

2 ACCEPTED SOLUTIONS
alanhodgson
Solution Supplier
Solution Supplier

Hey @kmuir.

 

Are you trying to return a number value or a date?

 

The below code can be used in a "Calculated Column" to achieve this as a number:

 

IF(FACT_ORDER.ORDER_ACTUAL_END_DATE > FACT_ORDER.ORDER_SCHED_END_DATE, (FACT_ORDER.ORDER_ACTUAL_END_DATE - FACT_ORDER.ORDER_SCHED_END_DATE), 0)

 

Make sure the data type of the new column is a number.

 

Hope this helps,

 

Alan

View solution in original post

@kmuir

 

What @alanhodgson and @JoeSQL suggetsed is DAX, you can also do it with Power Query. Just add a custom column in Query Editor.

 

5.PNG

 

= if [FACT_ORDER.ORDER_ACTUAL_END_DATE] > [FACT_ORDER.ORDER_SCHED_END_DATE] then [FACT_ORDER.ORDER_ACTUAL_END_DATE] - [FACT_ORDER.ORDER_SCHED_END_DATE] else 0

For more details, see: Power Query if Statements.

 

 

Regards,

View solution in original post

5 REPLIES 5
alanhodgson
Solution Supplier
Solution Supplier

Hey @kmuir.

 

Are you trying to return a number value or a date?

 

The below code can be used in a "Calculated Column" to achieve this as a number:

 

IF(FACT_ORDER.ORDER_ACTUAL_END_DATE > FACT_ORDER.ORDER_SCHED_END_DATE, (FACT_ORDER.ORDER_ACTUAL_END_DATE - FACT_ORDER.ORDER_SCHED_END_DATE), 0)

 

Make sure the data type of the new column is a number.

 

Hope this helps,

 

Alan

JoeSQL
Frequent Visitor

ColumnName = IF(FACT_ORDER.ORDER_ACTUAL_END_DATE > FACT_ORDER.ORDER_SCHED_END_DATE, FACT_ORDER.ORDER_ACTUAL_END_DATE - FACT_ORDER.ORDER_SCHED_END_DATE,0)

 

 

Should I add this code to query editor using Advance Editor. In report I am unable to add this logic as a new measure? Also in dax this syntax may not work. I need to knowcorrect syntax for adding to query editor as a new column.

@kmuir

 

What @alanhodgson and @JoeSQL suggetsed is DAX, you can also do it with Power Query. Just add a custom column in Query Editor.

 

5.PNG

 

= if [FACT_ORDER.ORDER_ACTUAL_END_DATE] > [FACT_ORDER.ORDER_SCHED_END_DATE] then [FACT_ORDER.ORDER_ACTUAL_END_DATE] - [FACT_ORDER.ORDER_SCHED_END_DATE] else 0

For more details, see: Power Query if Statements.

 

 

Regards,

@kmuir.

 

The formulas above are DAX, but I think you need to do this in MDX and use the IIF function (see here for details).

I am not a MDX expert so I can't write it for you, but I found some helpful links.

A similiar question was asked and answered here with the MDX provided.

 

Please post the end result if you figure it out so other can use it.

 

Thanks,

 

Alan

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.