List.Accumulate or what?

[greta]

Dear All, thank you for your time. 
I have a request that seems really complicated to me, I don't know if it's achievable. I've recreeated the data I'm working with in a simplified table that I'm posting here below.

As you can see, I have different projects that at different dates ("mese" column), have different status. 
The request is to calculate the number of months in which the project consecutively stays in "on hold", starting from the most recent. So for proj4 the result would be 2, for prog2, 1 and for prog1, 2.
I have tried in desktop with a measure,

OnHold_ =

var max_data = calculate(max(mese), removefilters(table))

var min_data = calculate(max(PjDev_HistoricalData[UpdatePP&CC]meseData[UpdatePP&CC]))

var _result = averagex(table, datediff(min_data, max_data, month))

return _result

but it oesn't display a cumulated value.
Any hint on how I could achieve this result?
Many thanks in advance

[AlienSx · Accepted solution]

Hello, @greta 

let
    Source = simplified_table,
    z = Table.Group(
        Source, {"prog"}, 
        {{
            "on_hold", 
            each List.PositionOf(
                Table.Sort(_, {"index", Order.Descending})[status],
                "on hold", 
                Occurrence.First, 
                (x, y) => x <> y
            )
        }}
    )
in
    z

[wdx223_Daniel]

Measure=CALCUATE(IF(MAXX(TOPN(1,Table,Table[index],DESC),Table[Status])="on hold",MAX(Table[index])-LASTNONBLANK(VALUES(Table[index]),IF(CALCULATE(MAX(Table[Status])="on hold",1))),ALLEXCPT(Table,Table[Project]))

[greta]

Thank you very much for your support!

[AlienSx · Accepted solution]

Hello, @greta 

let
    Source = simplified_table,
    z = Table.Group(
        Source, {"prog"}, 
        {{
            "on_hold", 
            each List.PositionOf(
                Table.Sort(_, {"index", Order.Descending})[status],
                "on hold", 
                Occurrence.First, 
                (x, y) => x <> y
            )
        }}
    )
in
    z

[greta]

I've adapted the query to the following dataset,

As follows:

let

    Source = Lista_OnHold,

    z = Table.Group(

        Source, {"MercatusCode"},

        {{

            "On_Hold",

            each List.PositionOf(

                Table.Sort(_, {"Index.1", Order.Descending})[ProjectStatusInternal.1],

                "On Hold",

                Occurrence.First,

                (x, y) => x <> y

            )

        }}

    )

in

    z
And I'm getting this result

Could you help me in understanding why?
Many thanks in advance

[AlienSx · Accepted solution]

@greta I did not consider the case when all values are On Hold. Try this instead:

let
    Source = Lista_OnHold,
    z = Table.Group(
        Source, {"MercatusCode"},
        {{
            "On_Hold",
            each 
                [lst = Table.Sort(_, {"Index.1", Order.Descending})[ProjectStatusInternal.1],
                pozz = List.PositionOf(lst, "On Hold", Occurrence.First, (x, y) => x <> y),
                zz = if pozz = -1 then List.Count(lst) else pozz][zz]
        }}
    )
in
    z

[greta]

Works perfectly, many thanks!

[greta]

Many thanks, that's exactly what I needed!