Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.


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 define the relation ship to the startdate in the base dataset and new table in the pbi report to able to plot the Days(see second table) on x-axis



StartDate     endDate  DaysSinceStartdate
10/15/2021 10/20/2021   5


need to expand the DaysSinceStartDate ( which is datediff of startDate and endDate) as below


stardate     enddate      Days
10/15/2021 10/20/2021 Day1
10/15/2021 10/20/2021 Day2
10/15/2021 10/20/2021 Day3
10/15/2021 10/20/2021 Day 4
10/15/2021 10/20/2021 Day 5

Any help on pointers on how to achieve this is  appreciated

Super User
Super User


This M code works

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Duration", each 1*([endDate]-[StartDate])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each {1..[Duration]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Duration"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"StartDate", type date}, {"endDate", type date}})
    #"Changed Type1"

Hope this helps.


Ashish Mathur

@Ashish_Mathur  Thanks this worked for me with but with an integer range use case. It was a small look up table that eventually results in 20.000 exploded rows, so not a very huge table. Performance is ok. 

Glad to know.  Thank you.

Ashish Mathur
Super User
Super User

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:

    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}})
    #"Added Prefix"


If you need to do this in DAX, then you can define a calculated table like this

    GENERATE ( Table1, GENERATESERIES ( 1, [DaysSinceStartDate] ) ),
    "StartDate", [StartDate],
    "EndDate", [EndDate],
    "Days", "Days " & [Value]

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

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.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors