Conditional Columns involving date and different column

[romovaro]

Hello.

I am trying to calculate a conditional column which returns a category.

I Have a column with dates called "Ops Forecast & Actuals) and different columns called:

"Backlog Entity Status"

"Project Health"

The rule I want to apply are:

When "Ops-Forecast & Actuals column" is 01/12/3000 and "Project Health column" is Red (text) then No Start

When "Ops-Forecast & Actuals column" is 01/12/3000 and "Backlog Entity Status column" is CL-New Client then Pre-implementation

When "Ops-Forecast & Actuals" is 01/12/3000 then On Hold

The rest we use the date on Ops Forecast & Actuals to group them by Fiscal Year (FY22, FY23 and >FY23)

ANy help is apprecited

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @romovaro ,

Here are the steps you can follow：

1. Create calculated column.

Backlog Analysis =
IF('Table'[Ops Forecast & Actuals]=DATE(3000,1,12),
SWITCH(
    TRUE(),
    'Table'[Project Health]="Red","No Start",
    'Table'[Backlog Entity Status]="CL - New Client","Pre-implementation",
    "On Hold"
    ),
    SWITCH(
        TRUE(),
        YEAR('Table'[Ops Forecast & Actuals])=2022,"FY22",
        YEAR('Table'[Ops Forecast & Actuals])=2023,"FY23",
        YEAR('Table'[Ops Forecast & Actuals])>2023,">FY23"
    ))

2. Result:

Best Regards,

Liu Yang

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

[romovaro]

Thanks Liu Yang.

It worked

[PhilipTreacy]

Hi @romovaro 

Just add a Custom Column in PQ and copy/paste the if ......... code

When you wrote The rest we use the date on Ops Forecast & Actuals to group them by Fiscal Year (FY22, FY23 and >FY23) I didn't realise you were asking for that to be done.  I thought you were describing what you do.  

You haven't provided any sample data and I don't know when your fiscal year runs (Jan-Dec?, Jul-Jun?) 

All the dates you've provided are in the year 3000?  Can you please give some examples of real data.

Also, I don't know if all rows have a category?  Are some without?

Regards

Phil

[romovaro]

Sorry Phil.

See below some data. I am trying to replicate the "Backlog Analysis" column. 

Ops Forecast has no blanks. Project Health and Backlog Entity Status have blanks.

Fiscal year starting July to Jun

Project Health	Backlog Entity Status	Ops Forecast & Actuals	Backlog Analysis
		01/01/2050	>FY23
		01/01/2050	>FY23
tbd	CL - New Client	01/05/2022	FY22
		01/01/2050	>FY23
tbd		01/01/2050	>FY23
tbd		01/12/3000	on Hold
Green		01/01/2022	FY22
tbd		01/01/2050	>FY23
n/a	CL - New Client	01/01/2050	>FY23
Green		01/01/2022	FY22
		01/01/2050	>FY23
tbd		01/12/3000	on Hold
		01/01/2050	>FY23
Red		01/12/3000	No Start
		01/01/2050	>FY23
Red		01/12/3000	No Start
Red		01/12/3000	No Start
		01/01/2050	>FY23
		01/01/2050	>FY23
tbd	CL - New Client	01/12/3000	Pre-Implementation
		01/01/2050	>FY23
		01/01/2050	>FY23
Red		01/12/3000	No Start
Red		01/01/2050	>FY23
Red		01/01/2050	>FY23
		01/01/2050	>FY23
tbd	CL - New Client	01/12/3000	Pre-Implementation
		01/01/2050	>FY23
Red		01/12/3000	No Start
Red		01/12/3000	No Start
Red		01/12/3000	No Start
Red		01/12/3000	No Start
tbd	CL - New Client	01/12/3000	Pre-Implementation
tbd	CL - New Client	01/12/3000	Pre-Implementation
tbd	CL - New Client	01/12/3000	Pre-Implementation

[PhilipTreacy]

Hi @romovaro 

Download example PBIX with the following code

In Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3NjAwUNJRCkpNAZJKsTrRSkZIwjAxQ1QxZx9dv9RyBeeczNS8ErACY8KalGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ops Forecast & Actuals" = _t, #"Project Health" = _t, #"Backlog Entity Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ops Forecast & Actuals", type date}, {"Project Health", type text}, {"Backlog Entity Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Ops Forecast & Actuals"] = #date(3000,12,1) then 

if [Project Health] = "Red" then "No Start"

else if [Backlog Entity Status] = "CL-New Client" then "Pre-Implementation"

else "On Hold"

else "")
in
    #"Added Custom"

In DAX

Column = IF([Ops Forecast & Actuals] = DATE(3000,12,1), 

        IF(CONTAINSSTRING([Project Health], "Red"), "No Start",
        
        IF(CONTAINSSTRING([Backlog Entity Status], "CL-New Client"), "Pre-Implementation","On-Hold")
        
        ), 
        
        "" )

Regards

Phil

[romovaro]

Hi Philip.

Looks awesome but having some issues. I am lost at the beg of your code.

The name of my table is called Start Forecast File. I already have Ops as "date", Project health as "text...+Do I need to include your whole code?

Table.TransformColumnTypes(Source,{{"Ops Forecast & Actuals", type date}, {"Project Health", type text}, {"Backlog Entity Status", type text}}

OR can just add Custom Column and paste some "if" formula? 

if [#"Ops Forecast & Actuals"] = #date(3000,12,1) then 

if [Project Health] = "Red" then "No Start"

else if [Backlog Entity Status] = "CL-New Client" then "Pre-Implementation"

else "On Hold"

else "")

My last part of the question was to use the rest of the rows--- using the date on Ops Forecast & Actuals to group them by Fiscal Year (FY22, FY23 and >FY23)

Thanks