topic Re: Filling up and down by catergory in DAX Commands and Tips

Filling up and down by catergory

michaeldonnelly — Fri, 22 Jul 2022 13:50:52 GMT

Just looking for some help - below is an example of data i have - im looking at sales over 21 days periods. Each promo has a unique ID. When no sales are made in store I dont get a price.

Im looking to fill in the blanks from the day after from each promo ID.

There is potentially a chance there is no sales from the last days of the promo, in these stances id like the blanks to be filled with the pervious days value.

The table below has blanks in prices, and ive created a column called wanted outcome to show what im trying to achieve. Any help with be greatly appreiceted as the simple fill options are no good for me here.

PROMO ID	RETAILER	STORE FORMAT	BRANCH NUMBER	STORE NAME	RETAILER_PRODUCT_CODE	Client	PRODUCT DESCRIPTION	DATE	Price	Promo Day	WANTED OUTCOME
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	21/04/2022		1	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	22/04/2022	£1.50	2	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	23/04/2022	£1.50	3	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	24/04/2022		4	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	25/04/2022	£1.50	5	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	26/04/2022	£1.50	6	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	27/04/2022	£1.50	7	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	28/04/2022	£1.50	8	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	29/04/2022	£1.50	9	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	30/04/2022	£1.50	10	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	01/05/2022		11	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	02/05/2022		12	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	03/05/2022		13	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	04/05/2022	£1.50	14	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	05/05/2022	£1.50	15	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	06/05/2022		16	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	07/05/2022	£1.50	17	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	08/05/2022		18	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	09/05/2022	£1.50	19	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	10/05/2022	£1.50	20	£1.50
1	Retailer 1	Format 1	Store 1	Store Name 1	1001	Client 1	Product 1	11/05/2022	£1.50	21	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	21/04/2022		1	£2.00
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	22/04/2022	£2.00	2	£2.00
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	23/04/2022	£1.50	3	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	24/04/2022	£1.50	4	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	25/04/2022	£1.50	5	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	26/04/2022	£1.50	6	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	27/04/2022	£1.50	7	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	28/04/2022	£1.50	8	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	29/04/2022	£1.50	9	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	30/04/2022	£1.50	10	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	01/05/2022	£1.50	11	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	02/05/2022	£1.50	12	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	03/05/2022	£1.50	13	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	04/05/2022	£1.50	14	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	05/05/2022	£1.50	15	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	06/05/2022	£1.50	16	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	07/05/2022	£1.50	17	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	08/05/2022	£1.50	18	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	09/05/2022	£1.50	19	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	10/05/2022	£1.50	20	£1.50
2	Retailer 1	Format 1	Store 2	Store Name 2	1001	Client 1	Product 1	11/05/2022	£1.50	21	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	21/04/2022	£1.50	1	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	22/04/2022	£1.50	2	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	23/04/2022		3	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	24/04/2022		4	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	25/04/2022	£1.50	5	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	26/04/2022	£1.50	6	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	27/04/2022	£1.50	7	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	28/04/2022		8	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	29/04/2022	£1.50	9	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	30/04/2022		10	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	01/05/2022		11	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	02/05/2022	£1.50	12	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	03/05/2022	£1.50	13	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	04/05/2022	£1.50	14	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	05/05/2022	£1.50	15	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	06/05/2022		16	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	07/05/2022		17	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	08/05/2022	£1.50	18	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	09/05/2022	£1.50	19	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	10/05/2022		20	£1.50
3	Retailer 1	Format 1	Store 3	Store Name 3	1001	Client 1	Product 1	11/05/2022		21	£1.50

Re: Filling up and down by catergory

serpiva64 — Fri, 22 Jul 2022 14:19:12 GMT

Hi,

you can try this way:

- In power query select the column Price,

- substitute space with null

- then fill up

and you obtain:

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

Re: Filling up and down by catergory

michaeldonnelly — Fri, 22 Jul 2022 14:22:11 GMT

The problem i will have with that is if Day 21 of PROMO 1 is blank and Day 1 of PROMO 2 is not it will fill promo 1 with the wrong value.

Need this to work per promo ID

Re: Filling up and down by catergory

random_Bi_User — Fri, 22 Jul 2022 14:32:57 GMT

Hi,

this dax would replace the empty price with the next days price with the same promo id. If its empty it will still show empty.
Do you need the rest of the prices filled too? If so with which value?

if(Tabelle1[Price]= BLANK(),LOOKUPVALUE(Tabelle1[Price],Tabelle1[PROMO ID],Tabelle1[PROMO ID],Tabelle1[DATE],DATEADD(Tabelle1[DATE],1,DAY)),Tabelle1[Price])

Also feel free to add more congruities in the lookupvalue. Hope this helps!

Re: Filling up and down by catergory

serpiva64 — Fri, 22 Jul 2022 14:53:55 GMT

Sorry,

if you want to do that after replace you have to group by:

then modify in advanced editor the red part of the code:

{{"AllRows", each _, type table

With this:

{{"AllRows", each Table.FillDown( Table.FillUp(_,{"Price"}),{"Price"}), type table

and it function.

You can refer to this video by Courbal

https://www.youtube.com/watch?v=QV98Jndj5Fo

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

Re: Filling up and down by catergory

v-cazheng-msft — Tue, 26 Jul 2022 02:54:04 GMT

Hi michaeldonnelly ,

To get the expected output, you need group your table by PROMO ID category firstly, then do the fill up and down based on the grouped table. The complete M code is as follows.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdjNasJAFAXgVwmui96ZiRqXMYlUqInESBciIjYLQSuIXfR5+iZ9sk6irWa4JXdzFuLM+POZCRyOs1p15nk2y7xp3Hnq5EkRTl+S3A4XRZYn3iTLZ2Fhp+M8TKNnL13Oxg8vp+EsefjYxn5VvIyKTZTF1Xp02JfvFzu4rXtxsojy6byYZqldjcOietf8vN+V9fPpePLi7acdv4ZpkcRetiyizBLrp1VHVVB52e4P5dmrJpPT+bi91MPF5XQuH0bp9nidKiL190PqFau8feyuY6165Pc0aW0nXvV2+/j+Ut0+wUj9QN4ou4p3DecavOs7W+zjyT53qX28O+DcAd4dcu4Q7wacG+DdEeeO4K4hzlUEh8nGVL8RU/icIu2a+Iwi45r4fLJ39G7ebyo+pazKwfiYooG7y/iIoiF7sfiMosC9WHw+0Yi9WHxAKeJgjQ8opVjYSSndCusmrFvhfwqc7hKObBa4mvotcFBXUuAQrs+5Pt6VtDiEK2lxCFfS4hCupMUhXEmLA7iyFgeAiQ1Jt8ohYM3CGg8bFsZnlqzUIWBRqUPAAxbGx5as2SHggIXxwSWrdwBYVu8QsKjemVbYNGHTCjfq3X2r8a7kkA7hGqfOGjzZcj6HICXNDuFKmh3ClTQ7hBs4tzbAk5JSB3Abpa7+H0hws/VUDmGKqhwCFlU5BCyqcghYVOUQcNv5HMIcuiY+nmQFDgGLChwAbhS4apfd7oYw3ZRq1Lb1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PROMO ID", Int64.Type}, {"RETAILER", type text}, {"STORE FORMAT", type text}, {"BRANCH NUMBER", type text}, {"STORE NAME", type text}, {"RETAILER_PRODUCT_CODE", Int64.Type}, {"Client", type text}, {"PRODUCT DESCRIPTION", type text}, {"DATE", type text}, {"Price", type text}, {"Promo Day", Int64.Type}, {"WANTED OUTCOME", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PROMO ID"}, {{"GroupedTable", each _, type table [PROMO ID=nullable number, RETAILER=nullable text, STORE FORMAT=nullable text, BRANCH NUMBER=nullable text, STORE NAME=nullable text, RETAILER_PRODUCT_CODE=nullable number, Client=nullable text, PRODUCT DESCRIPTION=nullable text, DATE=nullable text, Price=nullable text, Promo Day=nullable number, WANTED OUTCOME=nullable text]}}), #"Filled Up" = Table.FillUp(#"Grouped Rows",{"GroupedTable"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"PROMO ID"}), #"Expanded GroupedTable" = Table.ExpandTableColumn(#"Removed Columns", "GroupedTable", {"PROMO ID", "RETAILER", "STORE FORMAT", "BRANCH NUMBER", "STORE NAME", "RETAILER_PRODUCT_CODE", "Client", "PRODUCT DESCRIPTION", "DATE", "Price", "Promo Day", "WANTED OUTCOME"}, {"PROMO ID", "RETAILER", "STORE FORMAT", "BRANCH NUMBER", "STORE NAME", "RETAILER_PRODUCT_CODE", "Client", "PRODUCT DESCRIPTION", "DATE", "Price", "Promo Day", "WANTED OUTCOME"}) in #"Expanded GroupedTable"

Also, attached the pbix file as reference.

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 let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun