Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Allocating contract amount over multiple years

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 

allocate $
DAX
years
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    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.

4 Replies