Forum Discussion
Fill blanks with previous values
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😞
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 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...
4 Replies
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.
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 __ResultGreg_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_linkJ94 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...
