topic Re: Distributing 1 amount over different months in DAX Commands and Tips

Distributing 1 amount over different months

PDE — Tue, 28 Nov 2023 13:00:47 GMT

In my model I only have a 1 column table that lists several future periods (format YYYYMM).

I would like to distribute 1000 USD over these months. Each month should be allocated with 99 USD, until the whole amount has been fully allocated. I can't seem to find an elegant solution for this.

The desired output is pictured below.

Thanks in advance,

Re: Distributing 1 amount over different months

FreemanZ — Tue, 28 Nov 2023 14:19:19 GMT

Hi PDE ，

try to add two calculated columns like:

Allocation = VAR _periodmin = MIN([Period]) VAR _period = [Period] VAR _periodminSN = LEFT(_periodmin,4)*12+RIGHT(_periodmin, 2) VAR _periodSN = LEFT(_period,4)*12+RIGHT(_period, 2) VAR _gap = _periodSN -_periodminSN+1 VAR _quotient = QUOTIENT (1000, 99) VAR _mod = MOD(1000, 99) VAR _result = SWITCH( TRUE(), _gap<=_quotient, 99, _gap= _quotient+1, _mod, 0 ) RETURN _result

RT = IF( [Allocation]<>0, SUMX( FILTER( data, data[Period]<=EARLIER(data[Period]) ), [Allocation] ) )

it worked like:

Re: Distributing 1 amount over different months

Fowmy — Tue, 28 Nov 2023 15:38:46 GMT

PDE

I have also tried a soluton, please check. Two calculated columns need to be added to your table:

Allocation = var __amount = 1000 var __monthly = 99 var __cumm = __monthly + __amount - SUMX( FILTER( 'Table' , 'Table'[Month] <= EARLIER( 'Table'[Month] ) ), __monthly) var __val = MAX( min( __monthly , __cumm ) ,0) return __val

Running Total = IF( 'Table'[Allocation] > 0 , SUMX( FILTER( 'Table' , 'Table'[Month] <= EARLIER( 'Table'[Month] ) ) , [Allocation] ))

Re: Distributing 1 amount over different months

FreemanZ — Wed, 29 Nov 2023 01:38:01 GMT

Using a constant as the second parameter of SUMX. How brilliant!!!

Re: Distributing 1 amount over different months

ThxAlot — Wed, 29 Nov 2023 03:13:13 GMT

Allocation = MAX( MIN( 99, 1000 - COUNTROWS( WINDOW( 1, ABS, -1, REL, ALLSELECTED( data[Period] ) ) ) * 99 ), 0 )

RT = MIN( COUNTROWS( WINDOW( 1, ABS, 0, REL, ALLSELECTED( data[Period] ) ) ) * 99, 1000 )

Re: Distributing 1 amount over different months

Fowmy — Wed, 29 Nov 2023 06:33:07 GMT

FreemanZ

Thank you!

Use of QUOTIENT and MOD in your solution is a cleaver work.

Re: Distributing 1 amount over different months

PDE — Thu, 30 Nov 2023 13:01:10 GMT

Thanks Fowmy Works perfect.

Re: Distributing 1 amount over different months

PDE — Thu, 30 Nov 2023 13:02:06 GMT

Thank you!

Re: Distributing 1 amount over different months

PDE — Thu, 30 Nov 2023 13:03:18 GMT

Thank you!

Re: Distributing 1 amount over different months

PDE — Fri, 01 Dec 2023 09:47:26 GMT

ThxAlot , I eventually implemented your solution because it's more versatile (as a measure). If you would be so kind, have you got an idea how to offset the 'Allocation' by x (e.g. 3) rows? So instead of starting the allocation in period 202312, it should start in 202403. TIA

Re: Distributing 1 amount over different months

ThxAlot — Fri, 01 Dec 2023 13:04:08 GMT

Re: Distributing 1 amount over different months

PDE — Fri, 01 Dec 2023 14:32:53 GMT

Works like a charm, ThxAlot. Will take a while to fully understand but you helped me alot. Cheers