get the value of last 1 month and 3 months from the latestinput

[jeongkim]

Hi guys,

I put function in cell AZ2(in red) to get the last value from the latest input(not from today) using Lookup function.  

But I also would like to fill blank orange(AY2) for 2nd of last value to see up/down comparison(orange vs red)?

also would like to see the last input from (today) function.

fyi,

every month the blanks will be filled so need function to move the cell to be fetched automatically in the calculation. 

If I put particular cell in the function then we will need to edit it every single month. 

I tried with Index as below but not working,,,can anyone know what wrong is? it should've been 12.5%. 

Please help me on this, thanks in advance

Best

Kim

[v-yueyunzh-msft]

Hi , @jeongkim 

According to your case description, it seems that you are consulting a question about doing a logical operation on a formula for a single cell in an Excel product but the output is incorrect?

If it is convenient, can you submit this question to the forum of the Excel product instead of our Power BI forum, because we are experts in the Power BI product and not on the Excel product, and if you need one-on-one help from the experts of the Excel product, you need to go to the corresponding forum to submit a case:

Microsoft Excel Community

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jeongkim]

Thanks for the direction. 

meanwhile, would like to know how to get the last month as per last inout or (today) value from data query?

Data was unpivot as below, not specify the last month value separately. 

Thank you,

Kim

[v-yueyunzh-msft · Accepted solution]

Hi , @jeongkim 

According to your description, you want to add the last row in your table. Right?

Here are the steps you can refer to :

This is my test data:

We need to add acolumn to judge which is the last data:

You can put this mcode in your "Advanced Editor" to refer to :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcy5DQAhDATAVtDGBLb5uyBH7r+NMxI6bTrBnAMTXWmLIkPh+QcLMIYSUBhqQGVoAe2ByUs7w00Hw00n3D8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Start([Period],4) & Text.End([Period],2)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

We can get this table:

If you need to realize it in Power Query Editor:

(1)We can create a "Blank Query" in it:

Then we put this in the "Advanced Editor":

let
    Source = Table.TransformColumns(Table.SelectRows(Table,(x)=>x[Custom]=List.Max(Table[Custom])),{"Period",(x)=>"Last period"}) & Table
in
    Source

And then we can meet your need:

If you want to realize it in Power Bi Desktop:

(1)You can click "New Table" and enter this:

Table 2 = UNION( SELECTCOLUMNS( TOPN(1,'Table' , [Custom]) , "Period" , "Last Period" , "Value" , [Value] ) ,SELECTCOLUMNS('Table' , "Period",[Period] , "Value" , [Value]) )

(2)Then we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jeongkim]

wow thank you so much!

it looks professional but let me try it 🙂