Team, first off, I’m excited to be here and wish I had found Sam and his portal years ago. What a wonderful job he and his team have done. Here’s hoping somebody can help me close out my current dash requirements with assistance on this last calculation. I am in search of a means by which to calculate the total contract value of all the contracts for a customer that was lost in the year of the last expired contract but ignoring all contracts lost in years earlier.
A customer has ten different contracts that expired in 2017, 2018 & 2019. The value of all ten contracts is 100K. Of the ten contracts, only two expired in 2019, and their value is 20K. I want to be able to count this customer as lost with 2 contracts and calculate the total value of that customer lost in 2019 as 20K. I do NOT want to see the other years counts and dollars show up in any visualization.
I thought I was on the correct path with the below logic, but Power BI is telling me otherwise. Any guidance you could give to help wrap this up is greatly appreciated.
Final ACV = VAR LastContractDate = CALCULATE ( LASTDATE ( 'CONTRACT_Dataframe'[EXPIRATION_DATE] ) ) VAR LastContractYear = VALUE ( YEAR ( LASTDATE ( CONTRACT_Dataframe[EXPIRATION_DATE] ) ) ) RETURN CALCULATE ( SUM('CONTRACT_Dataframe'[ANNUAL_CONTRACT_VALUE]), FILTER ( 'CONTRACT_Dataframe', LastContractYear = year( LastContractDate) ), ALLEXCEPT('Contract_dataframe','CONTRACT_Dataframe'[EXPIRATION_DATE]) )
Any and all assistance is GREATLY appreciated!