Reaalocation of overlaping amounts in time with DAX?

[Sergun70]

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

My data:

	Start	End	Amount
Project 1	2018	2020	321
Project 2	2019	2021	141
Project 3	2018	2020	30
Project 4	2019	2022	220
…	…	…	…
Total			712

I equally allocated amounts like this

	Start	End	2018	2019	2020	2021	2022	Total
Project 1	2018	2020	107	107	107			321
Project 2	2019	2021		47	47	47		141
Project 3	2018	2020	10	10	10			30
Project 4	2019	2022		55	55	55	55	220
…	…	…	…	…	…	…	…	…
Total			117	219	219	102	55	712

Is it any solution to reallocate to nearly this

	Start	End	2018	2019	2020	2021	2022	Total
Project 1	2018	2020	135	113	73			321
Project 2	2019	2021		18	30	93		141
Project 3	2018	2020	8	11	11			30
Project 4	2019	2022		1	29	50	140	220
…	…	…	…	…	…	…	…	…
Total			143	143	143	143	140	712

[v-shex-msft]

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

Regards,

Xiaoxin Sheng

[Sergun70]

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?

[v-shex-msft]

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