Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Community Champion
Community Champion

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


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors