Need help in dax to calculate and create table, I have attached the pbix file

[mdaamirkhan]

Hi All

How will I calculate belwo things, I have attached the pbix file here. I will be very helpful if you can me to create this in the pbix file and share to me.

Contracts start date – minimum value from the contract Start Date per customer

Total relationship – number of months from the Contracts Start Date until today

Active relationship - number of months with active contract from the Contracts Start Date until today

Contract end date – maximum value from the contract End Date per customer

Future relationship – Number of months from today until Contracts end date

https://drive.google.com/file/d/1Io36Lg5jOSKegSFWqe3TQr_dlpxVxzCS/view?usp=sharing 

Output will be like this:

[mdaamirkhan]

Hi I need help on this 

Estimated revenue current year:  Number of month for which contract is active in current year multiplied by Average revenue based on End_Date
Estimated revenue current year +1 : Number of month for which contract is active in current year multiplied by Average revenue based on End_Date

[mdaamirkhan]

Thanks for helpin me. its for Future Relationship

How will I replace negative number with "Expired" 

e.g- if there in -20 or -50

[mdaamirkhan]

ok what about others if you can provide me 

Total relationship – number of months from the Contracts Start Date until today

Active relationship - number of months with active contract from the Contracts Start Date until today

Contract end date – maximum value from the contract End Date per customer

Future relationship – Number of months from today until Contracts end date

[selimovd · Accepted solution]

Hey @mdaamirkhan ,

check if the following measures are calculating the right values:

Contracts start date:

Contracts start date = CALCULATE(
    MIN( Input_Contract_Report[Start Date] ),
    ALLEXCEPT( Input_Contract_Report, Input_Contract_Report[Customer Name] )
)

Total relationship:

Total relationship = DATEDIFF( [Contracts start date], TODAY(), MONTH )

Active relationship:

Active relationship = CALCULATE( [Total relationship], Input_Contract_Report[Active] = "Yes" )

Contracts end date:

Contracts end date =
CALCULATE(
    MAX( Input_Contract_Report[End Date] ),
    ALLEXCEPT( Input_Contract_Report, Input_Contract_Report[Customer Name] )
)

Future relationship:

Future relationship = DATEDIFF( TODAY(), [Contracts end date], MONTH )

For the [Active relationship] I'm not really sure if I understood right. My measure returns the amount of month between start date and today for customers where the column Input_Contract_Report[Active] = "Yes"

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi