How to spread value between two dates ?

[Anonymous]

Hi everyone,

I need some help on my Power BI.

I am trying to spread the value of a project over several months, to know exactly how much I will have to produce every month.

For exemple, if I have a project sold at 60,000$ over 3 months, I will have to produce 20,000$ per month.

This is the data I have :

Project	Order Date	Delivery	Amount
1656KL	5-Jan-2020	4-May-2020	$100,000.00
1568MP	8-Feb-2020	3-Apr-2020	$85,000.00
1518KI	18-Feb-2020	30-Apr-2020	$120,000.00
5168GT	21-Mar-2020	21-Jun-2020	$50,000.00

This is the output i'm looking for :

Project Number	January	February	March	April	May	June	Total
1656KL	$21,666.67	$24,166.67	$25,833.33	$25,000.00	$3,333.33		$100,000.00
1568MP		$32,454.55	$47,909.09	$4,636.36			$85,000.00
1518KI		$18,333.33	$51,666.67	$50,000.00			$120,000.00
5168GT			$5,434.78	$16,304.35	$16,847.83	$11,413.04	$50,000.00

Of course every project doesn't start at the same date, not even on the first day of the month. So the amount allocated to each month has to take in consideration the number of day in each month.

I hope i'm clear enough, don't hesitate to ask me more questions about this.

Thank you very much for your help.

Nicholas

[Ashish_Mathur]

Hi,

I think your calculation of amount per day is incorrect.  For the first project, it should be 100000/121 = 826.4463 and not 100000/120=833.3333.  You may download my PBI file from here.

Hope this helps.

[EdwardWer]

Ashish:

The image you posted, shown below, had the correct "Total values".

The values in the file uploaded by someone else (URL below) had incorrect Total values, please see the screen shot below.

https://community.fabric.microsoft.com/oxcrx34285/attachments/oxcrx34285/power-bi-designer/439517/1/...

I was curious how you corrected this.

Thanks,

Edward

[Ashish_Mathur]

Hi,

I cannot understand your question.  Offer a simple explanation and show the expected result.

[EdwardWer]

Ashish:

Hello. I could not download the file with the link you provided.

Can you please advise what change you made to the DAX Measure "MonthAmount"?

Thanks,

Edward

[Ashish_Mathur]

Hi,

I do not have that file anymore.  Describe the question and show the expected result.

[v-lid-msft]

Hi @Anonymous ,

We can create a calculated table and a measure to meet your requriement:

Calculated Table:

DateTable = CALENDAR(MIN('Table'[Order Date]),MAX('Table'[Delivery]))

Measure:

MonthAmount = SUMX(DISTINCT('Table'[Project]),
CALCULATE(SUM ( 'Table'[Amount] ))
    / DATEDIFF ( MIN ( 'Table'[Order Date] ), MAX ( 'Table'[Delivery] ), DAY )
    * COUNTROWS(INTERSECT('DateTable',CALENDAR(MIN('Table'[Order Date])+1,MAX('Table'[Delivery])))))
    

Best regards,

[ORION007]

Hi!

Thank you so much for your post - it was of a great help!
Just one more thing, is there a way to make the spread throughout the months flat? Instead of calculating the volume per month by multipling the days, we have the entire year volume just divided by 12 and than, only showing this "flat volume" at the intersection of the months between "Order date" and "Delivery Date"?

Once again, thank you so much for your kind help!

[thomasgee]

This solution is really useful for my use case too. I don't suppose you would mind taking the time to explain how it works? Or link to the material where you got this from yourself?

[Anonymous]

Hi @v-lid-msft 

Thanks a lot for this solution, I managed to apply it on my Power BI.

Just two questions :

- What is your first picture about ? what are TAB_Litig_File and TAB_Prov ?

- Would it be possible to have a column with every amount for every month for each project ? I need this information to create another Matrix Table, in order to compare with my maximum production capacity.

Thanks again for your help.

Nicho

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @Anonymous ,

Sorry for that we put an unnessary screenshot, we selected wrong file when upload, it does not help in this post.

What is the column with every amount, Could you please share a mockup table based on the tables you have shared?

Best regards,

[Adamtall]

Hi,

This is kinda complexed. You need to create more rows in powerQuery.

Create a blank Query and copy this code.

Spoiler

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY49C8IwEIb/SgkdE7mLJmQtFMXWYge30CHaDIq2Elqh/94DaSTbvR8P91rL2jA+/G3KGGfn0PuQlW7yJEr/vH98WOgsXuM8TKzjlqFWuj6Rp0TlBiFBAomdaNyyihwBOABsAH6I0qZpKTBi769rayuKd4iIUSmBpj6SjykCCYMyeaNQm8OFAom0JtZIVXMcmqs/030B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project ", type text}, {"Order Date", type date}, {"Delivery", type date}, {"Amount", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","$","",Replacer.ReplaceText,{"Amount"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Amount"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".",",",Replacer.ReplaceText,{"Amount"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"Amount", type number}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type2", "Subtraction", each Duration.Days([Delivery] - [Order Date])),
#"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Custom", each { Number.From([Order Date]) +1 ..Number.From([Delivery] ) }),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Inserted Division" = Table.AddColumn(#"Changed Type3", "Division", each [Amount] / [Subtraction], type number)
in
#"Inserted Division"

When you understand It you can applie it to your orginal code.

Hope this can help you.

/Adam

[Anonymous]

Hi @Adamtall , Thank you very much for your answer.

I'm not sure to understand entirely your code, but i'm gonna try to apply it to my case and test it.

I can see on your screenshot it worked so it should on mine too !

I'll let you know, thanks again for your time.

Nicho