Forum Discussion

DataScope06's avatar
DataScope06
Icon for Helper I rankHelper I
3 years ago
Solved

Custom column - Select date from one column, or other date columns if null

My dataset has 3 different column dates: ORDER_DATETIME, ACTUAL_SHIPMENT_DATE, PROMISE DATE. I have a preferred hierarchy for selecting dates for a custom date column. If one of the date fields is null, then the next date field in the hierarchy should be used.

 

I have the SQL code but don't know the proper dax function. SQL is below

 

 CASE WHEN

ACTUAL_SHIPMENT_DATE is null then PROMISE_DATE
              WHEN PROMISE_DATE is null then ORDER_DATETIME
              else ACTUAL_SHIPMENT_DATE

END

 

The table looks like this

 

ACTUAL_SHIPMENT_DATEPROMISE_DATEORDER_DATETIME
1/1/20201/2/202012/31/2019
 3/1/2020 
 2/1/20212/2/2021
  4/1/2021

 

I would like a date column that shows this:

Date
1/1/2020
3/1/2020
2/1/2021
4/1/2021
Need Help
  • vojtechsima's avatar
    vojtechsima
    3 years ago

    Hello, DataScope06,
    this should work:

    Date = 
var ActualShipment = 'Table'[ACTUAL_SHIPMENT_DATE]
var Promise = 'Table'[PROMISE_DATE]
var Order_ = 'Table'[ORDER_DATETIME]

var check = 
SWITCH(TRUE(),
    ISBLANK(ActualShipment), IF(ISBLANK(Promise), Order_, Promise),
    ActualShipment
 )

return check

     

     

2 Replies

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    3 years ago

    Hello, DataScope06,
    this should work:

    Date = 
var ActualShipment = 'Table'[ACTUAL_SHIPMENT_DATE]
var Promise = 'Table'[PROMISE_DATE]
var Order_ = 'Table'[ORDER_DATETIME]

var check = 
SWITCH(TRUE(),
    ISBLANK(ActualShipment), IF(ISBLANK(Promise), Order_, Promise),
    ActualShipment
 )

return check

     

     
    • DataScope06's avatar
      DataScope06
      Icon for Helper I rankHelper I
      3 years ago

      I think your answer works, I will mark that as the solution, vojtechsima.

       

      Before you submitted the solution I did figure out how to make a custom column in Power BI, then I used this formula (copied from power query):

       

      if [ACTUAL_SHIPMENT_DATE] != null then [ACTUAL_SHIPMENT_DATE]

      else if [ACTUAL_SHIPMENT_DATE] = null then [PROMISE_DATE]

      else if [PROMISE_DATE] = null then [ORDER_DATETIME]

      else [ACTUAL_SHIPMENT_DATE]