Question on ETL in PowerBI Desktop

Hi Anonymous 

You may create a date table.And then create a measure like below.For further,please check the attached file.

Measure = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Sample'[Date] ),
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] < MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        ISBLANK ( SUM ( 'Sample'[Stock] ) ),
        CALCULATE (
            SUM ( 'Sample'[Stock] ),
            FILTER ( ALL ( 'Date' ), 'Date'[Date] = _maxdate )
        ),
        SUM ( 'Sample'[Stock] ) 
    )

Regards,

Cherie

Anonymous 

Please check this M/Power Query Solution
File attached as well

It adds missing dates and last non blank stock for each distributor and SKU

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZG7CsQgFET/xTrIfaiJ7bJdymWrkP//jU1AFscxlcg53IGZ4wjvl4YlfPbv/ViJYtFE6/XRcC4dt5Ebch+5N24P9xNyup+R0/2CPI18bdwf8jfklF8bT0/9CAp0QIcG70c0iv8F44pRcO4YhTSJSL2QJxEglEkECCvviMLGQ6JQeUkQTHgqFJS3QsF4LMm94DwWCleT5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Distributor = _t, SKU = _t, Date = _t, Stock = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor", type text}, {"SKU", type text}, {"Date", type date}, {"Stock", Int64.Type}}),
    SortedRows = Table.Sort(#"Changed Type",{{"Distributor", Order.Ascending}, {"SKU", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(SortedRows, {"Distributor", "SKU"}, {{"Date", each List.Distinct(SortedRows[Date])
, type list}}),
    ExpandedDates = Table.ExpandListColumn(#"Grouped Rows", "Date"),
    #"Merged Queries" = Table.NestedJoin(ExpandedDates,{"Distributor", "SKU", "Date"},SortedRows,{"Distributor", "SKU", "Date"},"ExpandedDates",JoinKind.LeftOuter),
    #"Expanded ExpandedDates" = Table.ExpandTableColumn(#"Merged Queries", "ExpandedDates", {"Stock"}, {"Stock"}),
    #"Added Custom" = Table.AddColumn(#"Expanded ExpandedDates", "Custom", each if [Stock]=null then 
let dist=[Distributor],
SKU_=[SKU], 
mydate=[Date]
in
Table.Max(
Table.SelectRows(#"Expanded ExpandedDates",each [Distributor]=dist and [SKU]=SKU_ and [Date]<mydate and [Stock]>0)
,
"Date")[Stock]
else
[Stock]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Distributor", Order.Ascending}, {"SKU", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Custom", "StockQty"}})
in
    #"Renamed Columns"