Forum Discussion
Solved
create a special date table
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 n...
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"
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"
