cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to SUM all rows in year of contract expiration year.

Team, 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.

 

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 calculate the total value of that customer lost in 2019 as 20K.  

 

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.  

 

Screen Shot 2020-06-04 at 9.10.22 AM.png

 

Scott

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: How to SUM all rows in year of contract expiration year.

Thank you for the engagement, in time we were able to solve the desired outcome by creating a separate table to pull the necessary values over for analysis and visualizations.   Below is the logic we used to grab the columns and values to get the roll up we were after. 

 

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
)

View solution in original post

5 REPLIES 5
Highlighted
Helper I
Helper I

Re: How to SUM all rows in year of contract expiration year.

It's worth noting; I'm trying NOT to have the 2017 & 2018 totals show up in visualization/graphs/tables.  The customer is considered lost to us on the year of the LAST expiration date.  I only want the represent the lost value for the additional contracts that did expire in that same year. 

Highlighted
Super User IV
Super User IV

Re: How to SUM all rows in year of contract expiration year.

@Smoody07 , You can join the expiration date with a date calendar and get the value for 2009.

If you have more than one date and one date is already join with Date table, refer this how to deal with more than one date

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

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: How to SUM all rows in year of contract expiration year.

Good day sir and thank you for the prompt feedback.

 

I do have a dates/calendar table of which the expiration date is related.  The time intelligence is actually my challenge as in my above example, every contract is being captured to show in my visualizations for each year it was lost.  That is actually not my goal as I cannot show a customer lost in multiple years.  Even though we lost revenue from the customer in 2017 and 2018, I only want them to show up in my visualizations for the last year they had an active contract, and only total the value of those contracts lost in 2019.  So when I graph it out, the customer, no longer with context to each contract row, shows lost in the month/year of the last expiring contract date (Which I have solved), but I need the total combined contract value of the contracts that expired the same year as the last contract.  Please see the example data image below.  I hope this helps clarify what I'm in search of.  I am combing through your provided links now as well in hopes of finding that nugget that helps put this report to rest.  Have a great day and thanks again for your support.

Screen Shot 2020-06-04 at 1.15.36 PM.png

Highlighted
Community Support
Community Support

Re: How to SUM all rows in year of contract expiration year.

Hi @Smoody07 

 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Helper I
Helper I

Re: How to SUM all rows in year of contract expiration year.

Thank you for the engagement, in time we were able to solve the desired outcome by creating a separate table to pull the necessary values over for analysis and visualizations.   Below is the logic we used to grab the columns and values to get the roll up we were after. 

 

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
)

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors