Forum Discussion

Joern's avatar
Joern
Regular Visitor
2 years ago

Line Chart - ratio between two data points

Hi,

 

i have a line chart showing unit prices with irregular dates. I like to show the ration between data points in the same chart. If i have regualar dates from Jan to Dec, this is normally no problem. But I struggle with the irregular dates, as they are not fixed and may change depending on the selection...  is there a function for last existing value or something similar, any suggestion on how to solve it?

 

 

Need Help
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Joern ,

    You might consider completing the missing unit price in PowerQuery:

    Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcvBCQBBCAPAXvJeyCa61Yj9t3EgeN+BqYIo+jpwIKFPwfRKxEgwVuyRZK5kjsjU/95D9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Unit Prices" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Unit Prices", Int64.Type}}),
    tb1 = #"Changed Type",
    alldate = Table.FromList(List.Transform({Number.From(List.Min(tb1[Date]))..Number.From(List.Max(tb1[Date]))},each Date.From(_)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Merged Queries" = Table.NestedJoin(alldate, {"Date"}, tb1, {"Date"}, "alldate", JoinKind.LeftOuter),
    #"Expanded alldate" = Table.ExpandTableColumn(#"Merged Queries", "alldate", {"Unit Prices"}, {"Unit Prices"}),
    #"Filled Down" = Table.FillDown(#"Expanded alldate",{"Unit Prices"})
in
    #"Filled Down"

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group