How to calculate count & Sum only the last year's lost contracts

DAX DAX Calculations
1

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.

Example:

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!

Scott M.

2

My bad, I failed to add a screen shot of my example data:

image
image4232×834 580 KB

3

Welcome to the forum @smoody, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

4

Are you getting no result or is power bi giving you an error message… any chance of you attaching a pbix file

5

Thank you for the engagement. We were not getting the refined results we were after. In the end though, we were successful in accomplishing our goal by creating a separate table that would both pull out the necessary values from our transaction tables and calc the necessary output. Here is the logic that is now in place.

LostCustomers =
FILTER (
ADDCOLUMNS (
ADDCOLUMNS (
ADDCOLUMNS (
ALL ( ACCOUNTS_Dataframe[CORPORATE_NAME] ),
“Total Contracts”, CALCULATE ( COUNTROWS ( CONTRACT_Dataframe ) ),
“Active Contracts”, CALCULATE (
COUNTROWS ( CONTRACT_Dataframe ),
CONTRACT_Dataframe[IS_ACTIVE] = “TRUE”
),
“Last Contract Expired”, CALCULATE ( MAX ( CONTRACT_Dataframe[EXPIRATION_DATE] ) )
),
“Last Year of Contract”, YEAR ( CALCULATE ( MAX ( CONTRACT_Dataframe[EXPIRATION_DATE] ) ) )
),
“Value in Last Year”, CALCULATE (
[Total ACV],
FILTER ( DatesTable, DatesTable[Year] = [Last Year of Contract] )
)
),
[total contracts] > 0
&& [active contracts] = 0
)

Thanks again for engaging with us.