Forum Discussion

leandross89's avatar
leandross89
Icon for Helper II rankHelper II
4 years ago
Solved

Last month status

Hello Everybody,

 

I have a problem that I´m not getting finish it. Could you help me please?

 

I want to create a collumn that show me the "last month status".

 

ProdutoDataStatus2Last month status
122/01/2022 00:00Alerta 
131/01/2022 00:00AlertaAlerta
112/02/2022 00:00defeito 
128/02/2022 00:00defeitodefeito
123/03/2022 00:00defeito 
124/03/2022 00:00Alerta 
126/03/2022 00:00AlertaAlerta

 

I tried to do it using summarize, but I didn´t get. Below is what I want to do: (this example I inform only 1 product,but there are more than 1, like 2,3,4)

 

ProdutoDataStatus2Last moth status
131/01/2022 00:00AlertaAlerta
128/02/2022 00:00defeitodefeito
126/03/2022 00:00AlertaAlerta
General Comment
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    leandross89 , Create a new column

     

    var _month = eomonth([Data],0)
    var _max = maxx(filter(table, [Produto] =earlier([Produto]) && eomonth([Data],-1) = _month ), [Data])
    return
    maxx(filter(table, [Produto] =earlier([Produto]) && [Data] = _max ), [Status2])

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    leandross89 , Create a new column

     

    var _month = eomonth([Data],0)
    var _max = maxx(filter(table, [Produto] =earlier([Produto]) && eomonth([Data],-1) = _month ), [Data])
    return
    maxx(filter(table, [Produto] =earlier([Produto]) && [Data] = _max ), [Status2])

    • leandross89's avatar
      leandross89
      Icon for Helper II rankHelper II
      4 years ago

      Dear Thank you a lot.  I had changing this part "[Data],-1" to [Data],0), it worked very well. How Can I study dax, do you know some content/website/youtube to share with? 

  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    See if this code in Power Query fulfills your requirement:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzLSNzIwMgKyHXNSi0oSlWJ1YFLGhjikjPQN4bpSUtNSM0vykeSMLHDJGesbGeORM8FhHVDKDF0qFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Produto = _t, Data = _t, Status2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Produto", Int64.Type}, {"Data", type date}, {"Status2", type text}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Data]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"Month"}, {{"MaxDate", each List.Max([Data]), type nullable date}, {"Status", each List.Max([Status2]), type nullable text}, {"Product", each List.Max([Produto]), type nullable number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Month"})
in
    #"Removed Columns"