Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
amitchandak
Super User
Super User

@Anonymous , 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-trend/ba-p/882970

 

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Anonymous
Not applicable

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

Hi @Anonymous 

 

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.
Anonymous
Not applicable

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
)

Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.