Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
14 | |
14 | |
9 |