Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
Proud to be a Super User!
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"
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |