Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
How can I achieve this in DAX?
Solved! Go to Solution.
Table 3 = GENERATESERIES(SELECTCOLUMNS('Table 2',"min",'Table 2'[d1]),SELECTCOLUMNS('Table 2',"max",'Table 2'[d2]))
Table 3 = GENERATESERIES(SELECTCOLUMNS('Table 2',"min",'Table 2'[d1]),SELECTCOLUMNS('Table 2',"max",'Table 2'[d2]))
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
102 | |
68 | |
46 | |
37 | |
37 |