Creating a Calculated Column on PowerBi

Question

I'm looking to create a Calculated column on PowerBi. The output I require is named as RequiredOutput.

Your assistance please..

ID	Date	OldStatus	NewStatus	RequiredOutput
12345	30/09/2021			Self
12345	31/10/2021			Self
12345	30/11/2021	Self	Team	Team
12345	31/12/2021			Team
12345	31/01/2022	Team	Self	Self
12345	28/02/2022			Self
56789	30/09/2021			Team
56789	31/10/2021			Team
56789	30/11/2021	Team	Self	Self
56789	31/12/2021			Self
56789	31/01/2022	Self	Team	Team
56789	28/02/2022			Team

Answer 1

Power Query
Via Power Query, you can group by ID, then fill-up OldStatus and fill-down NewStatus, then create a new column based on NewStatus ?? OldStatus.

let
  Source = YourTable,
  #"Replace to null" = Table.ReplaceValue(Source, "", null, Replacer.ReplaceValue, {"NewStatus", "OldStatus"}),
  #"Grouped rows" = Table.Group(#"Replace to null", {"ID"}, {{"Rows", each _, type nullable table[ID = nullable text, Date = nullable text, OldStatus = nullable text, NewStatus = nullable text]}}),
  #"FillUp OldStatus" = Table.TransformColumns(#"Grouped rows", { "Rows", each Table.FillUp(_, {"OldStatus"})}),
  #"FillDown NewStatus" = Table.TransformColumns(#"FillUp OldStatus", { "Rows", each Table.FillDown(_, {"NewStatus"})}),
  #"Add Status" = Table.TransformColumns(#"FillDown NewStatus", { "Rows", each Table.AddColumn(_, "Status", each [NewStatus]??[OldStatus]) }),
  #"Expanded Rows" = Table.ExpandTableColumn(#"Add Status", "Rows", {"Date", "Status"}, {"Date", "Status"})
in
  #"Expanded Rows"

DAX
You can also try the following in DAX, though depending on your data model size, you may experience slower refresh times:

Status = 
  var thisID = [ID]
  var thisDate = [Date]

  var filterPrev = 
    FILTER(
      'YourTable',
      [ID] = thisID && [Date] >= thisDate && NOT ISBLANK('YourTable'[OldStatus]) && 'YourTable'[OldStatus] <> ""
    )
  var filterNext = 
    FILTER(
      'YourTable',
      [ID] = thisID && [Date] <= thisDate && NOT ISBLANK('YourTable'[NewStatus]) && 'YourTable'[NewStatus] <> ""
    )

  var prevStatus = CALCULATE(MAX('YourTable'[OldStatus]), TOPN(1, filterPrev, [Date], ASC) )
  var nextStatus = CALCULATE(MAX('YourTable'[NewStatus]), TOPN(1, filterNext, [Date], DESC) )

  RETURN COALESCE(nextStatus, prevStatus)