create a special date table

[marcp]

Hi everyone

I have a table of items with a reference date. For example

Item Refdate

ItemA 2017/01/01

ItemB 2017/06/01

ItemC 2018/02/26

This table is frequently updated with new items

I need to create a new unique table like this :

Item Date

ItemA 2017/01/01 (reference date for this item)

Item A 2017/01/02 (next day)

... (and so on)

ItemA today's date

ItemB 2017/06/01

ItemB 2017/06/02

...

ItemB today's date

ItemC 2018/02/26

ItemC 2018/02/27

...

ItemC today's date

Obviously, this table must be updated every day

-to add the new today's date at each item

-to add new items

I trie to figure out something with calendarauto but I don't know how to add the Item name and loop on all items

Thank you for your help

Regards

Marc

[danextian · Accepted solution]

Hi @marcp,

If there are a quite a handful of items, I can imagine how tedious it would be to achieve your requirement using DAX so I suggest doing it in M.

I was able to achieve what's on the screenshot above using the M script below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjA0V4rViVZyAgqYIQs4AwWM9I3MQCIWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Start Date" = _t]),
    //returns Today's date based on server time or device's time
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Start Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "List of Dates", each List.Dates([Start Date],Number.From( DateTime.Date(DateTime.LocalNow())-[Start Date]), #duration(1, 0, 0, 0)), type list),
    #"Expanded List of Dates" = Table.ExpandListColumn(#"Added Custom", "List of Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded List of Dates",{{"List of Dates", type date}})
in
    #"Changed Type1"

[marcp]

Hi @danextian

That's awesome, exactly what I needed. 

Thank you so much !

Rgards

Marc