Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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]))
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
83 | |
75 | |
64 | |
39 | |
34 |
User | Count |
---|---|
107 | |
56 | |
52 | |
48 | |
40 |