Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Here is how my data is setup:
Orderid | amount_per_month | start | end | |||
1 | 30 | 4/10/2021 | 4/10/2020 | |||
2 | 25 | 12/1/2020 | 12/1/2021 | |||
3 | 40 | 6/1/2021 | 6/1/2022 | |||
4 | 90 | 7/1/2021 | 7/1/2022 |
My intended result for Order ID 1:
Orderid | amount_per_month | Date | ||
1 | 30 | 4/10/2020 | ||
1 | 30 | 5/10/2020 | ||
1 | 30 | 6/10/2020 ... | ||
1 | 30 | 4/10/2021 |
Does anyone know if this is possible within a Dax measure?
I basically need it to spread out the dates between the start and end date for each order.
Any help is appreciated!
Solved! Go to Solution.
So I actually ended up finding this page which goes over a couple of different dax patterns
So I actually ended up finding this page which goes over a couple of different dax patterns
@Anonymous ,
Try this Power Query solution:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYvBCcAwDAN38TtgS0kbOovJ/mvUbnHajziOk7tAmnSLGQpTGv8MWc2FYXjEgIpKit+k5yn1WXYjn2CEuDKYXzB3sG4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Orderid = _t, amount_per_month = _t, start = _t, end = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Orderid", Int64.Type}, {"amount_per_month", Int64.Type}, {"start", type date}, {"end", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([start], Duration.TotalDays([end] - [start]) + 1, #duration(1, 0, 0, 0))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"start", "end"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type1"
Use GENERATESERIES.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
42 | |
41 | |
37 | |
19 | |
19 |