cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

 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

3 REPLIES 3
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]
)
```

Regards,

Xiaoxin Sheng

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

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?

Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.