Forum Discussion

RobX's avatar
RobX
Advocate I
5 years ago
Solved

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) ...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

     

    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] )
)

     

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

 

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] )
)

 

  • RobX's avatar
    RobX
    Advocate I
    5 years ago

    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!