Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowGet inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.
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]))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
71 | |
68 | |
50 | |
30 |
User | Count |
---|---|
119 | |
101 | |
73 | |
65 | |
40 |