Allocating contract amount over multiple years

[Anonymous]

Hello,

I have an interesting question -

Working with contracts that are generally 1 year but sometimes they go multiple years.

Each contract has a contract id, start date, end date, and amount.

I need to show contract expenditure by year.  Straight forward when contract is for one year i.e. Jan 1, 2022 thru Dec 31, 2022.

How do i allocate the amount by year if a contract starts on Feb 1, 2021 and ends on Jan 31, 2024. 

Many Thanks!

Pav 

[v-xiaosun-msft · Accepted solution]

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.

[Anonymous]

Thank You!

I really appreciate it.

Pavan

[amitchandak]

@Anonymous , refer if my blog on a similar topic can help

Measure way

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[Anonymous]

Thank You!

I really appreciate it.

Pavan