Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Joern
Regular Visitor

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?

 

Joern_0-1705371886366.png

 

1 REPLY 1
v-cgao-msft
Community Support
Community Support

Hi @Joern ,

You might consider completing the missing unit price in PowerQuery:

vcgaomsft_0-1705475437064.png

vcgaomsft_1-1705475451408.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.