Forum Discussion

ErickReris's avatar
ErickReris
Frequent Visitor
1 year ago
Solved

Replace Date Values in a single column

I have 4 columns of data in Power BI.

A column called Initial Forecast, which informs the initial delivery forecast for the order.

A column called Forecast Delivery, which reports the forecast data reported by the system.

A column called delivery date, which tells me when the order was delivered.

It's a column called Entry Date, which tells me when that order entered the system.

I want to create a column called End Date, which tells me the last data present in these columns, but in the following order:

 

Initial Forecast >> TMS Forecast >> Delivery Date >> Entry Date

 

As the dates are released, he must consider the date that is available according to the priority of dates above

General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,

    Thanks for the solution michaelu1  offered, and i want to offer some more information for user to refer to.

    hello  ErickReris  , you can refer to the following sample.Sample data 

    Create a calculated column.

    Column =
VAR a = [Initial Forecast] & "|" & [Forecast Delivery] & "|" & [Delivery Date] & "|" & [Entry Date]
VAR b =
    ADDCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( a ) ),
        "Item", PATHITEM ( a, [Value] )
    )
VAR c =
    MINX ( FILTER ( b, [Item] <> BLANK () ), [Value] )
RETURN
    DATEVALUE ( MINX ( FILTER ( b, [Value] = c ), [Item] ) )

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

3 Replies

  • michaelu1's avatar
    michaelu1
    Icon for Advocate II rankAdvocate II
    1 year ago

    I'm not sure exactly what you want, but assuming that the columns are null until the data gets populated, it seems you can use variables and then use a switch:

    VAR _InitialForecast = [Initial Forecast]
    VAR _TMSForecast = [TMS Forecast]
    VAR _DeliveryDate = [Delivery Date]

    VAR _EntryDate = [Entry Date]
    VAR _Switch = 
    Switch(
        True(),
              ISBLANK(_TMSForecast), _InitialForecast,
              ISBLANK(_DeliveryDate ), _TMSForecast,

              ISBLANK(_EntryDate ), _DeliveryDate ,

              _EntryDate
    )
    RETURN
    _Switch

     

    I wrote that freehand, but you should get the idea.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for the solution michaelu1  offered, and i want to offer some more information for user to refer to.

    hello  ErickReris  , you can refer to the following sample.Sample data 

    Create a calculated column.

    Column =
VAR a = [Initial Forecast] & "|" & [Forecast Delivery] & "|" & [Delivery Date] & "|" & [Entry Date]
VAR b =
    ADDCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( a ) ),
        "Item", PATHITEM ( a, [Value] )
    )
VAR c =
    MINX ( FILTER ( b, [Item] <> BLANK () ), [Value] )
RETURN
    DATEVALUE ( MINX ( FILTER ( b, [Value] = c ), [Item] ) )

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.  Do you want a calculated column or a measure solution?