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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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