Forum Discussion
Fill blanks with previous values
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...
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
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
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...