Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
smpa01
Super User
Super User

DAX to generate series on datetime

Is it possible to generate a series on DAX where my starting and ending values are datetime and I want to generate a series for the same time on different days.

i.e. starting on 

1/1/2021 4:00:00 PM

ending on 

1/5/2021 4:00:00 PM

and I want DAX to generate the following

 

| Custom              |
|---------------------|
| 1/1/2021 4:00:00 PM |
| 1/2/2021 4:00:00 PM |
| 1/3/2021 4:00:00 PM |
| 1/4/2021 4:00:00 PM |
| 1/5/2021 4:00:00 PM |

 

An equivalent power queryquery output is following

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUMLEyMAAihQBfJR2gqCmGaGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [d1 = _t, d2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"d1", type datetime}, {"d2", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([d1],Duration.Days([d2] - [d1])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

 

If I have a table X as following

 

| Column1             | Column2             |
|---------------------|---------------------|
| 1/1/2021 4:00:00 PM | 1/5/2021 4:00:00 PM |

 

with following 2 measures

 

Measure 2 = MIN([Column1])
Measure 3 = MAX([Column2])

 

and I try GENERATESERIES as following

 

Table 2 = GENERATESERIES([Measure 2],[Measure 3],5)

 

it does not give me what I want

Capture.PNG

 

How can I achieve this in DAX?

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
smpa01
Super User
Super User

Table 3 = GENERATESERIES(SELECTCOLUMNS('Table 2',"min",'Table 2'[d1]),SELECTCOLUMNS('Table 2',"max",'Table 2'[d2]))

 

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

1 REPLY 1
smpa01
Super User
Super User

Table 3 = GENERATESERIES(SELECTCOLUMNS('Table 2',"min",'Table 2'[d1]),SELECTCOLUMNS('Table 2',"max",'Table 2'[d2]))

 

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.