Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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, 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,
Solved! Go to Solution.
@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] ))
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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
)
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
@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
Works like a charm, ThxAlot. Will take a while to fully understand but you helped me alot. Cheers
Thank you!
@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] ))
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Using a constant as the second parameter of SUMX. How brilliant!!!
@FreemanZ
Thank you!
Use of QUOTIENT and MOD in your solution is a cleaver work.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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:
Thank you!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
31 | |
23 | |
22 | |
22 |