Forum Discussion
balak
Microsoft Employee
4 years agoHow to generate explode the datediff between 2 dates to multiple rows
I have 2 columns , startdate and processeddate and I want to explore the datediff between these 2 dates to muliptle rows like below in Dax and i am assuming this will be separate table and need to ...
AlexisOlson
Super User
4 years agoIf you do this in the query editor, you can create a list from 1 to DaysSinceStartDat, expand to new rows, and prepend "Day" to that number.
M Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNzTVNzIwMlTSAXGMDCCc2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "DaysSinceStartDate", each Duration.Days([EndDate]-[StartDate]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Days", each {1..[DaysSinceStartDate]}, type list),
#"Expanded Days" = Table.ExpandListColumn(#"Added Custom", "Days"),
#"Added Prefix" = Table.TransformColumns(#"Expanded Days", {{"Days", each "Day " & Text.From(_, "en-US"), type text}})
in
#"Added Prefix"
If you need to do this in DAX, then you can define a calculated table like this
SELECTCOLUMNS (
GENERATE ( Table1, GENERATESERIES ( 1, [DaysSinceStartDate] ) ),
"StartDate", [StartDate],
"EndDate", [EndDate],
"Days", "Days " & [Value]
)