Fill blanks with previous values

[J94]

Hi everyone, 

I want to show the sales per week. When there are no sales made for a week, I want to show the sales from the week before (or the week before that, etc. when there are sales made again. 

I created a simple sample dataset:

Week	Brand	Product_bk	Sales
1	A	AA1	10
1	C	CA1	50
1	B	BA1	25
2	C	CB2	100
2	B	BB2	45
2	A	AB2	20
3	C	CC3	150
3	B	BC3	65
3	A	AC3	30
4	C	CD4
4	B	BD4	250
4	A	AD4	200
5	C	CE5
5	B	BE5
5	A	AE5
6	C	CF6
6	B	BF6
6	A	AF6	100

I had this measure for Brand level (based on a similar topic already asked: https://community.fabric.microsoft.com/t5/Desktop/Fill-blank-values-with-previous-value/td-p/1210628😞

Sales (fillblanks) =

VAR _weekwithsales =

    CALCULATE(MAX('Tabel'[Week]),

    FILTER(

        ALLEXCEPT('Tabel', Tabel[Brand]),

        'Tabel'[Week]<=MAX('Tabel'[Week]) && 'Tabel'[Sales]<>BLANK()

    )

    )

RETURN

CALCULATE(

    SUM([Sales]),

    'Tabel'[Week] = _weekwithsales, ALLEXCEPT('Tabel', Tabel[Brand])

)

This works, but I need it on Product_bk level. And that doesn't work, because the table doesn't have an empty row for every week for Product_bk where there are no sales. I did try to add blank rows for every week in Query Editor, but because the real dataset is larger, it wasn't able to handle that request. 
So, is there a way to change the measure for Product_bk? 

[J94]

@Greg_Deckler thanks, I already thought it would be difficult to solve it with a measure. I will have a different approach and change the source instead. 

[Greg_Deckler]

@J94 Seems like it should be:

Measure = 
    VAR __Week = MAX( 'Table (5)'[Week] )
    VAR __Product = MAX( 'Table (5)'[Product_bk] )
    VAR __Sales = MAX( 'Table (5)'[Sales] )
    VAR __PreviousWeek = 
        MAXX(
            FILTER( 
                ALL( 'Table (5)' ),
                [Product_bk] = __Product && [Sales] <> BLANK() 
            ),
            [Week]
        )
    VAR __Previous = MAXX( FILTER( ALL( 'Table (5)' ), [Product_bk] = __Product && [Week] = __PreviousWeek ), [Sales] )
    VAR __Result = IF( __Sales = BLANK(), __Previous, __Sales )
RETURN
    __Result

[J94]

@Greg_Deckler , thanks for your reply. The Measure doesn't differ from the original Sales column unfortunalety. 

I added my file in Google Drive, so you can freely access it:
https://drive.google.com/file/d/1RGzJP_5movy2b3BmwH_fLJkc_A25fVLa/view?usp=drive_link

[Greg_Deckler · Accepted solution]

@J94 Well, yeah, because in your data you don't have any previous sales for the products that are blank. You are basically running into an Auto Exist issue. Essentially you are attempting to invent data that doesn't exist. The measure is not going to calculate on rows it feels are invalid. They were supposed to have fixed the auto exist issue with allowing you to select "Independent" as the "Value filter behavior" for the semantic model but it doesn't work in all cases, like this one. Essentially, Power BI isn't even attempting to calculate the measure.

You could add a column and that should work but, again, you don't have any previous week sales in your data for the products that have blanks. So...