Forum Discussion

balak's avatar
balak
Microsoft Employee
4 years ago

How 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 ...
Need Help
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

If 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]
)
balak's avatar
balak
Microsoft Employee
4 years ago

Thank much AlexisOlson ,

 

1) regarding the Dax approach,  I have the "DayssinceStartDate" as part of the dataset and Dax is not recognizing this field, in the,  GENERATESERIES ( 1, [DaysSinceStartDate] ) ) ? unless its part of aggregate function

 

2) regarding the approach1 (custom), is this going to be  new dataset and how to refer to the source dataset assuming its 'table1' and has the fields, startdate,enddate and dayssinceStartDate ? 

 

3)Assumimg I am going with the dax way, need to have the startDate columm from table1 along with DaysSinceStartdate to be able to define relationship to other table which has just the startDate (single  value)  

 

End goal is I need to able to plot the DaysSinceStartDate on the x-axis of chart with the counts from the table1 which has the startdate and enddate fields

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    It gives me an angry red underline too but it does work. I think this is probably a bug in the IntelliSense.

     

    I'm sorry but I don't quite follow what you're getting at in 2 and 3.