Conditional Columns involving date and different column

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

 

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