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
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
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.