Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |