Forum Discussion
PDE
2 years agoRegular Visitor
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...
- 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 __valRunning Total = IF( 'Table'[Allocation] > 0 , SUMX( FILTER( 'Table' , 'Table'[Month] <= EARLIER( 'Table'[Month] ) ) , [Allocation] )) - 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 ) - 2 years ago