Calculating Annual Contract based on Start Date and End Date

[Kolumam]

I have a table that has a start date of the contract starting from March 2017 and ending at April 2018. I want to calculate the yearly contract price. See the table below.

Contract Name	Start Date of Contract	End Date of Contract	Price
X	1/3/2017	31/3/2018	387
X	1/4/2018	31/3/2019	387
X	1/4/2019	31/12/2020	250

I want to calculate what was the annual price for each year and for all the contracts. How do I do this?

@amitchandak @parry2k @mahoneypat @harshnathani 

[harshnathani]

HI @Kolumam ,

Let me know if you are lookin for this. If not, pls provide the expected o/p.

You can try this measure.

Annual Contract Price =
VAR diff_stdat_endate =
    DATEDIFF (
        MAX ( 'Table'[Start Date of Contract] ),
        MAX ( 'Table'[End Date of Contract] ),
        MONTH
    )
RETURN
    DIVIDE (
        12,
        diff_stdat_endate
    )
        * MAX ( 'Table'[Price] )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Kolumam]

Hi @harshnathani 

Thanks but I need to plot the values using a Year column. How do I do this for each contract?

[amitchandak]

@Kolumam , can share expected output.

Current Contract  = CALCULATE(Maxx(FILTER(Contract,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Contract[End Date]) || Contract[End Date]>max('Date'[Date]))),(Contract[Price])),CROSSFILTER(Contract[Start Date],'Date'[Date],None))

If you create a date table and join it with the start date above will give max price for the year. Year should be there in Date table

[Kolumam]

Hi @amitchandak @harshnathani 

As per the formula given by @harshnathani , I tried it but the issue is the date. When I filter the year to 2020, it should ideally show the "Annual Contract Price" as (450*7/12) which is 262.5. How do I add a proper date axis and filter it with year?

[amitchandak]

@Kolumam , Just see if this blog can help

You will get a monthly amount

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Or this file can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Kolumam]

Hi @amitchandak 

The solution looks very complex.

I am looking for a yearly contract price calculation for each contract.