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...
  • Fowmy's avatar
    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
    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
    )