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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors