Split value over month using a distribution key - HELP

Anonymous — Mon, 11 Jan 2021 13:29:44 GMT

Hello everybody,

I really hope you can help me with this problem, which seems pretty complicated for me.

What I want to to is split the weightamount out on different month in the furture based on the expected number of hours.

I have following distribution key for expected hours:
0-500 = 2 month

500-1500 = 4 month

1500 - 4000 = 6 month

4000 - above = 8 month.

So forexample: in the above observation the start date is 01/05 and expected hours of 6,48 therefore weightamount should be split over 2 month -> Month 5 = 2592 and Month 6 = 2592.

Anyone who have a solution to this, or can point me in the right direction? Should i maybe create a seperate calendar table?

Important note: If it is the first of the month then it should be allocated to that month. So for the above exapmle because it is the first of the monst (01/05) then it should be allocated to month 5 and 6, but if the start date wat 07/05 then i should have been allocated to month 6 and 7.

The final output should be a graph like this, so i can see how many hours we expect to have en the future:

Thanks!

Re: Split value over month using a distribution key - HELP

Stachu — Wed, 13 Jan 2021 19:08:09 GMT

let's start with the number of months
add the following table

Low	High	Months
0	500	2
500	1500	4
1500	4000	6
4000	99999999	8

and add following calculated column to your table

NrOfMonths = VAR __expected = 'Table'[Expected Hours] VAR __relevantSplit = FILTER ( 'Split', __expected > 'Split'[Low] && __expected <= 'Split'[High] ) RETURN CALCULATE ( MAX ( 'Split'[Months] ), __relevantSplit )

It should give you the expected number of months for each row. If you can add the table using M then we can also replace 99999999 with +infinity

now for the allocation - do you want to create a calculated table, or are you looking for a measure that would assign the values to particular dates?

topic Split value over month using a distribution key - HELP in DAX Commands and Tips

Split value over month using a distribution key - HELP

Re: Split value over month using a distribution key - HELP