How to split a value across multiple months *unevenly*.

[AJ_Clark]

Hi all, I've been stuck with this problem for a little while and I'm hoping one of the many experts on here will be able to assist me. 

We are forecasting sales and gross profitabiliy based on a number of scenarios and for the purposes of this exercise, have a table like the followign as a part of an output: 

You can assume the the 'Date' below is the Sales Date, but it comes from a larget master date table that has all of the usual inclusions such as month number etc. 

Now, we have mocked up another very simple table with the month id and % to claim against. But this can easily be changed to whatever is simplest (numbe of days etc.)

The 1 - 6 in the above table is the month numbers to 'add' to the sales date to initiate that part of the claim, we're happy to change this to days or any other value that will simplify this work. 

So if we look at the above example (July and August 2019) the desired output would be something lie this: 

Date	July-19	August-19	...
% of income claimed	$2,946,115.95 (15% of $19,640,773)	$4,910,193.25 (25% of $19,640,773)	...
		$3,591,961.20 (15% of $23,946,408)	...
		$8,502,154

I hope that makes sense! Open to all suggestions. Thank you for your help

[v-xicai · Accepted solution]

Hi @AJ_Clark ,

You may create month column in your fact data table first of all, then create relationship between the fact data table and your Division table on Month field.

Month= MONTH(Data[Sale Date])

Then create measure like DAX below. You may choose Table visual or Matrix visual to display the result.

Contract Amount to be claimed= Data[Contract Gross Profit]*RELATED(Division[Claim Amount])

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Can you post your source data as text so that this can be recreated easily?

[AJ_Clark]

Here's a link to a Google Drive Sheet with some data (dummy input and the split with expected output) what is missing is the date table but I figure those are mostly generated via a script. 

https://docs.google.com/spreadsheets/d/1_0KE2h2p4lYDHr8cC85mYQD40jpca9KwOXr4_6t0qpA/edit?usp=sharing...