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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors