Allocating contract amount over multiple years

Hi Anonymous ,

According to your description, I made a sample, and here is my solution.

Data sample:

Create a measure to calculate the total days between “start date” and “end date”.

Total days =
DATEDIFF ( MAX ( 'Tabelle1'[start date] ), MAX ( 'Tabelle1'[end date] ), DAY ) + 1

Create three measures to calculate days of every year.

2022 =
DATEDIFF ( MAX ( 'Tabelle1'[start date] ), DATE ( 2022, 12, 31 ), DAY ) + 1

2023 =
IF (
    MAX ( 'Tabelle1'[end date] ) > DATE ( 2022, 12, 31 ),
    DATEDIFF ( MAX ( 'Tabelle1'[start date] ), DATE ( 2023, 12, 31 ), DAY ) + 1 - 'Tabelle1'[2022]
)

2024 =
IF (
    MAX ( 'Tabelle1'[end date] ) > DATE ( 2023, 12, 31 ),
    DATEDIFF ( MAX ( 'Tabelle1'[start date] ), MAX ( 'Tabelle1'[end date] ), DAY ) + 1 - 'Tabelle1'[2022] - 'Tabelle1'[2023]
)

Create three measures to calculate the amount of each year.

amount of 2022 =
DIVIDE ( MAX ( 'Tabelle1'[amount] ), 'Tabelle1'[Total days] ) * 'Tabelle1'[2022]

amount of 2023 =
DIVIDE ( MAX ( 'Tabelle1'[amount] ), 'Tabelle1'[Total days] ) * 'Tabelle1'[2023]

amount of 2024 =
DIVIDE ( MAX ( 'Tabelle1'[amount] ), 'Tabelle1'[Total days] ) * 'Tabelle1'[2024]

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.