Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
PDE
Regular 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, 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,

 

PDE_0-1701176314308.png

 

3 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

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

Fowmy_0-1701185914115.png

 








Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

ThxAlot
Super User
Super User

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
)

ThxAlot_0-1701227619174.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

ThxAlot_0-1701435879799.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

11 REPLIES 11
ThxAlot
Super User
Super User

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
)

ThxAlot_0-1701227619174.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



PDE
Regular Visitor

@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

ThxAlot_0-1701435879799.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



PDE
Regular Visitor

Works like a charm, ThxAlot. Will take a while to fully understand but you helped me alot. Cheers

PDE
Regular Visitor

Thank you!

Fowmy
Super User
Super User

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

Fowmy_0-1701185914115.png

 








Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

PDE
Regular Visitor

Thanks @Fowmy Works perfect.

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.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

FreemanZ
Super User
Super User

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:

FreemanZ_0-1701181103230.png

 

Thank you!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors