Forum Discussion

kmuir's avatar
kmuir
New Member
9 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • alanhodgson's avatar
    alanhodgson
    9 years ago

    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

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    kmuir

     

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

     

     

    = 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,

alanhodgson's avatar
alanhodgson
Solution Supplier
9 years ago

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