Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
In my PowerBI model I have a table with distributors' stocks:
My problem is that some distributors (DB) give stock only once a month and some can provide stock on weekly\daily basis. This results in the following pivot table
What I need is: populate stocks in the original table for each DB for each SKU for each day available in the table from the latest previous available date but not earlier than 3 months old. The resulting pivot should look like this (populated cells highlighted yellow):
Thank you.
Solved! Go to Solution.
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"
Hi @Anonymous
Have you checked my solution before?Please let me know if the measure could help you.
Regards,
Cherie
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"
Wow! Thanks a lot! This looks super cool, yet a bit complicated for me.
From what I understand, this is the code I need to type in the advanced editor part of the Power Query tool in MS PowerBI. Would it be possible to do step-by-step guide on how to work this out?
Sure..
I will do it when i get back in few hours
@Anonymous
My apologies for late reply
Here are steps
1) First step is to Group the Table by Columns "Distibutor and SKU" and add a list of each possible date for Distributor/SKU Combination.
2) Once we have all the dates for a Distributor/SKU Combination, we can merge it back to original query and get the missing dates.
3) Next we add a custom column to get the last stock balance for each Distributor/SKU Combination and for each date that was added
You click on the steps in Query Editor to see them in action
@Anonymous
Could you copy paste data from first image?
Please find sample factStock table below
Distributor | SKU | Date | Stock |
DB1 | SKU1 | 26.02.2019 | 1 |
DB1 | SKU2 | 26.02.2019 | 2 |
DB1 | SKU3 | 26.02.2019 | 3 |
DB2 | SKU1 | 26.02.2019 | 4 |
DB2 | SKU2 | 26.02.2019 | 5 |
DB2 | SKU3 | 26.02.2019 | 6 |
DB2 | SKU4 | 26.02.2019 | 7 |
DB3 | SKU1 | 26.02.2019 | 8 |
DB3 | SKU2 | 26.02.2019 | 9 |
DB4 | SKU1 | 26.02.2019 | 10 |
DB4 | SKU2 | 26.02.2019 | 11 |
DB1 | SKU1 | 01.03.2019 | 12 |
DB1 | SKU2 | 01.03.2019 | 13 |
DB1 | SKU3 | 01.03.2019 | 14 |
DB1 | SKU1 | 04.03.2019 | 15 |
DB1 | SKU2 | 04.03.2019 | 16 |
DB1 | SKU3 | 04.03.2019 | 17 |
DB2 | SKU1 | 04.03.2019 | 18 |
DB2 | SKU2 | 04.03.2019 | 19 |
DB2 | SKU3 | 04.03.2019 | 20 |
DB3 | SKU1 | 04.03.2019 | 21 |
DB3 | SKU2 | 04.03.2019 | 22 |
DB4 | SKU1 | 05.03.2019 | 23 |
DB4 | SKU2 | 05.03.2019 | 24 |
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |