Calculate the number of projects with red status for 3 consecutive snapshots

[GNOHC]

Hi, 

May I know how can I calculate the number of projects that have red status for 3 consecutive snapshots.

There is no date in my database.

Below is an example of my data. Based on the data below, only Project 1 & Project 3 satisfy the condition of having red status for 3 consecutive snapshots. Hence, when I filter for Snapshot = 2023-01, the correct count will be 2 projects.

Snapshot Project ID  Status 

2023-01   Project1    Red

2022-12   Project1    Red

2022-11   Project1    Red

2023-01   Project2    Green

2022-12   Project2    Yellow

2022-11   Project2    Red

2023-01   Project3    Red

2022-12   Project3    Red

2022-11   Project3    Red

[ACLH]

Hi rajulshah,

Thank you for the above m query.

For the example below, the above m query will show that there are 4 red projects as it does not take into account whether the snap shot is consecutively one month apart.

Rightfully, there should only be 1 project that is red for 3 consecutive snapshots based on the below example.

2023-01   Project1    Red

2022-12   Project1    Red

2022-11   Project1    Red

2023-01   Project2    Red

2022-12   Project2    Red

2022-05   Project2    Red

2023-01   Project3    Red

2022-10   Project3    Red

2022-09   Project3    Red  

2023-01   Project4    Red

2022-12   Project4    Red

2022-05   Project4    Red

I tried adapting "Custom" by adding the blue text shown below. But, it does not work.

try Value.Equals([Project ID],#"Added Index"[Project ID]{[Index] - 1}) and Value.Equals([Discipline],#"Added Index"[Discipline]{[Index] - 1}) and Value.Equals(1,Number.Round(Number.From(([Snapshot]–#"Added Index"[Snapshot]{[Index]-1})/(365.25/12),0))) otherwise null

Could you advise how should I reflect that there should also be a month difference between snapshots in order for "Custom" to be TRUE.

[rajulshah]

@GNOHC 

You can try the following m query and then take the count of the last column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1MFTSUQooys9KTS4BMYNSU5RidcCSRrqGRvgkcetENRZkiHtRamoedoNBzMjUnJz8cuxmG+Ez2xifkzElsemMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Snapshot = _t, #"Project ID" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Snapshot", type date}, {"Project ID", type text}, {"Status", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Project ID", Order.Ascending}, {"Status", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index1", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try Value.Equals([Project ID],#"Added Index"[Project ID]{[Index1] - 1}) and Value.Equals([Status],#"Added Index"[Status]{[Index1] - 1}) otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Count", each if [Custom] = true then if Value.Equals([Custom],#"Added Custom"[Custom]{[Index1] - 1}) then 1 else null else null)
in
    #"Added Custom1"

Hope this helps.