Get Previous Value on the same measure

DAX DAX Calculations
1

I ask for your support, I have the following scenario:

I have a table with this columns: Year, Month, N° Row, Weight, Pending

The months and years are arranged chronologically.

I need to do a dax with the following criteria:

image
image1011×869 172 KB

Basically I need to reference the previous row’s result of the same measure and so on.

Has anyone by any chance had this type of scenario?

If so, how did you get a solution?

I share this example
PreviousRow.csv (3.0 KB)

2

Hi

I have only been able to make different measurements for each row and at the end I use a switch to join these measurements

image
image1028×705 140 KB

Has anyone by any chance had this type of scenario?

If so, how did you get a solution?

I share the PBIX
PreviousRow.pbix (74.4 KB)

1 Like
3

Hi @jemh_100 ! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

4

Hi @jemh_100,
I was trying to solve your query in the forum a few days after posting it, but I couldn’t get the correct result.
Today I saw the following publication of Jihwan Kim that @BrianJ shared on Linkedin:

and finally I have been able to reach the expected result. I hope it’s not too late.

image
image1095×594 45.3 KB 

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZXdbtswDEbfJde1QX4UKelZil60XTZkaJsh/QG2p58c1xFlCQggSLGPKYqHur8/8OHuQLNoGTBrNgGlYMuMQGU4vh0v5zLy99K0vpApRQby8uT682sPd/cHVDDPLCLRSBz45/HpsqKxoXEjpSghGGm/sKClomlmzgCZ5Ip+fbz8W8CygcVFaUmUGBx3swUcKnjimRCTlr+1kh+fLqeXMoaNHG7k66sld4pIFq44bXCSMlEyRB/o3yXO7QOTetoVYQ3CLCGlRFwRvz/fTgtjy+xkHSO2DOJyyBqSZ7xcGVtkU+wYqWEE4miWs8v546/z+8cC2bhT6iC5zQdFoRyig7wf/3ycjq+lMMpkW59yB2JqSKyZcjktl5bz88fnirk+u1Yu9SBX/tNSZoGgUHdEb+evW0RcJeAehQaVQjZkyS7NP07PFXUrekaPkgYFIUklqvUN7qXkaRBOaBgEoOwtSoV0AvI0CEXbUDSLBN0KkHvZ2Ml2g1gDSYyIsiVUyM4rbr1aIbGBBMvGzGo+Em8TD2xiV8ZlyFFYArmc7GzigU2cG0YqxyzFas9obOKBTSDPCGoSNag74L1NPLAJ7CFKVIxMHCtkZBMPbEJbuWylxWgit6XeJh7ZhLZuqTSqUCJy+R3ZxCOb0LZi5nKtqKRUUSObeGQT2jac2Iwphu9SQ68TBjrBlzBmU1bG7dLBSCcMdEJsMciggArZyYSBTEgNIhsRCQWukJ1MGMgE34pl5phDKo3PfCReJgxkEmoYFLMsV5Nj7GzCwCbhFmLliouk8JBGJ+x1evgP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [N = _t, Ponderacion = _t, Pendiente = _t, #"Mes Año" = _t, Mes = _t, N_Mes = _t, Año_Mes = _t, E0 = _t, E1 = _t, E2 = _t, EXCEL = _t]),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"N", Int64.Type},
      {"Ponderacion", type number},
      {"Pendiente", type number},
      {"Mes Año", Int64.Type},
      {"Mes", type text},
      {"N_Mes", Int64.Type},
      {"E0", type number},
      {"E1", type number},
      {"E2", type number},
      {"EXCEL", type number}
    }
  ),
  #"Inserted Parsed Date" = Table.AddColumn(
    #"Changed Type",
    "Parse",
    each Date.From(DateTimeZone.From([Año_Mes])),
    type date
  ),
  #"Added Ponderacion x Pendiente" = Table.AddColumn(
    #"Inserted Parsed Date",
    "PonderacionxPendiente",
    each [Ponderacion] * [Pendiente],
    type number
  ),
  #"Added Result" = Table.TransformColumns(
    Table.AddColumn(
      #"Added Ponderacion x Pendiente",
      "Result",
      (L) =>
        List.Accumulate(
          List.Range(#"Added Ponderacion x Pendiente"[PonderacionxPendiente], 0, L[N]),
          0,
          (x, y) => x * (1 - L[Ponderacion]) + y
        )
    , type number),
    {"Result", (L) => L}
  )
in
    #"Added Result"

Regards

PreviousRow_JAFP.pbix (89.1 KB)

3 Likes