DAX Fill Up - limit

[VanessaCain]

Hi,

I'm working with a large set of daily data that gives prices for the iron ore industry.

I've been given a DAX formula to 'fill up' gaps in my historic data i.e. to roll over the previous day's price was not collected for any reason. However, it's also 'filling up' my current date's data. So this means that before the price is published for the day, it's appearing as if it has already been collected but just rolling over the previous day's date.

Is there a way to restrict the 'filling up' to not include today's date? But for the data to be populated with the actual update for the day when it is published.

It would be ok if the fill up ran up to a certain static date, which I could update monthly (it's reasonably rare that the data isn't collected).

Please see the formula below (it also restructures the data).

Thanks,

Vanessa

let fnDateTable= (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>

  let

    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,

    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),

    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

    InsertDayWeek = Table.AddColumn(RenamedColumns, "DayInWeek", each Date.DayOfWeek([Date]))

in

    InsertDayWeek,

    #"Invoked FunctionfnDateTable" = fnDateTable(#date(2012,01,01), Date.From(DateTime.LocalNow()),null),

    #"Merged Queries" = Table.NestedJoin(#"Invoked FunctionfnDateTable",{"Date"},Seaborne1,{"指标编码"},"Data_",JoinKind.LeftOuter),

    #"Expanded Data_" = Table.ExpandTableColumn(#"Merged Queries", "Data_", {"指标编码", "IO_0000195735", "IO_0000195734", "ST_0000030714", "IO_0000456590", "ST_0000030707", "ST_0000030708", "ST_0000030710", "ST_0000030709", "ST_0000030727", "IO_0000857787", "IO_0000136979", "IO_0000456591", "ST_0000030712", "ST_0000030721", "ST_0000030722", "ST_0000030716", "IO_0000136977", "IO_0000128461", "IO_0000857786", "ST_0000030701", "ST_0000030702", "ST_0000030703", "ST_0000030704", "ST_0000030705", "ST_0000030706", "ST_0000030724", "IO_0000221454", "IO_0000221453", "ST_0000030725"}, {"指标编码", "IO_0000195735", "IO_0000195734", "ST_0000030714", "IO_0000456590", "ST_0000030707", "ST_0000030708", "ST_0000030710", "ST_0000030709", "ST_0000030727", "IO_0000857787", "IO_0000136979", "IO_0000456591", "ST_0000030712", "ST_0000030721", "ST_0000030722", "ST_0000030716", "IO_0000136977", "IO_0000128461", "IO_0000857786", "ST_0000030701", "ST_0000030702", "ST_0000030703", "ST_0000030704", "ST_0000030705", "ST_0000030706", "ST_0000030724", "IO_0000221454", "IO_0000221453", "ST_0000030725"}),

    #"Sorted Rows" = Table.Sort(#"Expanded Data_",{{"Date", Order.Descending}}),

    #"Filled Up" = Table.FillUp(#"Sorted Rows",{"指标编码", "IO_0000195735", "IO_0000195734", "ST_0000030714", "IO_0000456590", "ST_0000030707", "ST_0000030708", "ST_0000030710", "ST_0000030709", "ST_0000030727", "IO_0000857787", "IO_0000136979", "IO_0000456591", "ST_0000030712", "ST_0000030721", "ST_0000030722", "ST_0000030716", "IO_0000136977", "IO_0000128461", "IO_0000857786", "ST_0000030701", "ST_0000030702", "ST_0000030703", "ST_0000030704", "ST_0000030705", "ST_0000030706", "ST_0000030724", "IO_0000221454", "IO_0000221453", "ST_0000030725"}),

    #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column153"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"指标编码", "LastDate"}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Date", "LastDate","DayInWeek"}, "Attribute", "Value"),

    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Code"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Value", Int64.Type}, {"LastDate", type date}}),

    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"LastDate"}),

    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Day Type", each if [DayInWeek] = 5 then "Week End" else if [DayInWeek] = 6 then "Week End" else "Week Days"),

    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column",{"DayInWeek"})

in

    #"Removed Columns2"

[parry2k]

@VanessaCain the parameter you are passing Date.From(DateTime.LocalNow()) , subtract one day from this using Date.AddDays function, read here about this function.

[VanessaCain]

Hi, thank you so much for responding!

But will that mean that if e.g. the data is updated at 6:30pm today, it won't appear in my report until tomorrow?

Delivering the updated data as soon as it's updated at the source is much more important that back-filling the old dates for the purpose of the tool.

Thanks again for your help!

[parry2k]

@VanessaCain assuming going forward there is no missing data and doesn't require backfill, correct?

[VanessaCain]

Thanks again @parry2k 

Correct - the missing data is just a historic problem. 

So moving forwards we don't need any fill ups. However, we do need the data be updated as soon as it's updated at the source, rather than the following day....