Forum Discussion

greta's avatar
greta
Frequent Visitor
2 years ago
Solved

List.Accumulate or what?

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
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    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
  • AlienSx's avatar
    AlienSx
    2 years ago

    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

7 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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's avatar
      greta
      Frequent Visitor
      2 years ago
      Many thanks, that's exactly what I needed!
    • greta's avatar
      greta
      Frequent Visitor
      2 years ago

      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's avatar
        AlienSx
        Super User
        2 years ago

        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
  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    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's avatar
      greta
      Frequent Visitor
      2 years ago

      Thank you very much for your support!