Forum Discussion

PDE's avatar
PDE
Regular Visitor
2 years ago
Solved

Distributing 1 amount over different months

Hi 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, unt...
Running totals and cumulatives
  • Fowmy's avatar
    Fowmy
    2 years ago

    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] ))

     








  • ThxAlot's avatar
    ThxAlot
    2 years ago
    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
)

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
2 years ago

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:

 

  • PDE's avatar
    PDE
    Regular Visitor
    2 years ago

    Thank you!