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,

kmuir's avatar
kmuir
New Member
9 years ago

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.

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
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,