cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grantculp
Advocate I
Advocate I

Dax allocate amount between two dates

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!

1 ACCEPTED SOLUTION
grantculp
Advocate I
Advocate I

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

 

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

View solution in original post

3 REPLIES 3
grantculp
Advocate I
Advocate I

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

 

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

View solution in original post

camargos88
Super User III
Super User III

@grantculp ,

 

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"


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



daxer-almighty
Solution Sage
Solution Sage

Use GENERATESERIES.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors