Forum Discussion
RobX
5 years agoAdvocate I
Fill in Missing Dates
I have a table of weekly values that I want to break out into daily values.
Data is similar to below but is in Excel.
CREATE TABLE #tempTable (
Brand VARCHAR(100),
Date DATE,
Spend int)
INSERT INTO #tempTable
(
Brand,
Date,
Spend
)
VALUES
('A', '1/7/2021', 100),
('A', '1/14/2021', 150),
('A', '1/21/2021', 200),
('B', '1/7/2021', 250),
('B', '1/14/2021', 200),
('B', '1/21/2021', 900),
('C', '1/7/2021', 200),
('C', '1/14/2021', 100),
('C', '1/21/2021', 50)
SELECT
*
FROM #tempTable
I want to generate records for every day in between the available dates for each Brand so that the end result looks like this:
I found this suggestion on in an old StackOverflow post, but it only works to create a table with just Date and Spend.
FullTable =
ADDCOLUMNS(
CALENDAR(MIN(#tempTable[Date]), MAX(#tempTable[Date])),
"Quantity",
LOOKUPVALUE(
#tempTable[Spend],
#tempTable[Date],
MAXX(
FILTER(#tempTable, #tempTable[Date] <= EARLIER([Date])),
[Date]
)
)
)
Does anyone have any idea how to expand it to include an additional dimension?
let Source = #table({"Brand","Date","Spend"},{ {"A", "1/7/2021", 100}, {"A", "1/14/2021", 150}, {"A", "1/21/2021", 200}, {"B", "1/7/2021", 250}, {"B", "1/14/2021", 200}, {"B", "1/21/2021", 900}, {"C", "1/7/2021", 200}, {"C", "1/14/2021", 100}, {"C", "1/21/2021", 50}}), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Transformed Column" = Table.TransformColumns(#"Changed Type", {"Date", each List.Reverse(List.Dates(_,7,-#duration(1,0,0,0)))}), #"Expanded Date" = Table.ExpandListColumn(#"Transformed Column", "Date") in #"Expanded Date"Expansion_DAX = GENERATE ( Base, ADDCOLUMNS ( GENERATESERIES ( 0, 6 ), "All Date", Base[Date] - [Value] ) )
2 Replies
- CNENFRNLCommunity Champion
let Source = #table({"Brand","Date","Spend"},{ {"A", "1/7/2021", 100}, {"A", "1/14/2021", 150}, {"A", "1/21/2021", 200}, {"B", "1/7/2021", 250}, {"B", "1/14/2021", 200}, {"B", "1/21/2021", 900}, {"C", "1/7/2021", 200}, {"C", "1/14/2021", 100}, {"C", "1/21/2021", 50}}), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Transformed Column" = Table.TransformColumns(#"Changed Type", {"Date", each List.Reverse(List.Dates(_,7,-#duration(1,0,0,0)))}), #"Expanded Date" = Table.ExpandListColumn(#"Transformed Column", "Date") in #"Expanded Date"Expansion_DAX = GENERATE ( Base, ADDCOLUMNS ( GENERATESERIES ( 0, 6 ), "All Date", Base[Date] - [Value] ) )- RobXAdvocate I
The DAX example was exactly what I needed. Though one of the dates did get duplicated, but I'll find a way around that.
Thanks!