topic Re: Allocating contract amount over multiple years in DAX Commands and Tips

Allocating contract amount over multiple years

Anonymous — Thu, 06 Oct 2022 21:22:52 GMT

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

Re: Allocating contract amount over multiple years

amitchandak — Fri, 07 Oct 2022 03:41:22 GMT

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

Re: Allocating contract amount over multiple years

v-xiaosun-msft — Fri, 07 Oct 2022 07:29:56 GMT

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.

Re: Allocating contract amount over multiple years

Anonymous — Wed, 19 Oct 2022 19:16:04 GMT

Thank You!

I really appreciate it.

Pavan

Re: Allocating contract amount over multiple years

Anonymous — Wed, 19 Oct 2022 19:16:23 GMT

Thank You!

I really appreciate it.

Pavan