Dax allocate amount between two dates

[Anonymous]

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!

[Anonymous · Accepted solution]

So I actually ended up finding this page which goes over a couple of different dax patterns

https://www.daxpatterns.com/events-in-progress/#

[camargos88]

@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"

[daxer-almighty]

Use GENERATESERIES.