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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Sergun70
Frequent Visitor

Reaalocation of overlaping amounts in time with DAX?

I'm finding the solution to reallocate amounts in time to smooth maximum sums. Is it possible to do this with DAX?

 

My data:

 

 StartEndAmount
Project 120182020321
Project 220192021141
Project 32018202030
Project 420192022220
Total  712

 

I equally allocated amounts like this

 

 StartEnd20182019202020212022Total
Project 120182020107107107  321
Project 220192021 474747 141
Project 320182020101010  30
Project 420192022 55555555220
Total  11721921910255712

 

Is it any solution to reallocate to nearly this

 

 StartEnd20182019202020212022Total
Project 12018202013511373  321
Project 220192021 183093 141
Project 32018202081111  30
Project 420192022 12950140220
Total  143143143143140712

 

 

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Sergun70,

 

You can refer to below formula to create a new calculated table with expand year ranges, then use this table to create matrix to achieve your requirement.

 

Expand Table = 
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN (
            'Sample',
            GENERATESERIES ( MIN ( 'Sample'[Start] ), MAX ( 'Sample'[End] ), 1 )
        ),
        [Value] >= [Start]
            && [Value] <= [End]
    ),
    "Project", [Project],
    "Start", [Start],
    "End", [End],
    "Amount", [Amount] / ( [End] - [Start] + 1 ),
    "Range", [Value]
)

9.PNG

 


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanx Xiaoxin Sheng,

 

I've already managed to equally allocate amounts in time with dax formula.

I can't reallocate my data to smoth overlapping intervals as in my second table.

Is there any solution or algotithm to solve this?

 

 

Hi @Sergun70,

 

>>I can't reallocate my data to smoth overlapping intervals as in my second table.

Is there any offset or increase percent to change divided values? I need a additional column with percent or other similar offset values to achieve snapshot 3.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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