Forum Discussion

CierrahW's avatar
CierrahW
Regular Visitor
3 years ago
Solved

Total Customers By Year

I'm trying to find the number of customers by year. The graph shows how many customers that started service in that year (red line). What I need is for the previous year values to add up each year so that the number of customers is cumulative. 

 

This query gives me the chart below. How can I edit it to give me the cumulative values? 

 

let
Source =redacted for privacy,
#"Master Customer List - MASTER_Sheet" = Source{[Item="Master Customer List - MASTER",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Master Customer List - MASTER_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parent Account Name", type text}, {"Vertical Segment", type text}, {"Date Added", Int64.Type}, {"CTC", type text}, {"BDM", type text}, {"Account Executive", type text}, {"New Logo?", type text}, {"Lead Source", type text}, {"Contract Vehicle Customer?", type text}, {"Proposal Team Used?", type text}, {"BD Team Push?", type text}, {"UL2050?", type text}, {"BD Initiative?", type text}, {"Which Contract Vehicle?", type text}, {"ACS", type text}, {"VMS", type text}, {"Fire", type text}, {"Other", type text}, {"Notes:", type any}, {"Created By", type text}, {"Modified By", type text}, {"Created", type number}, {"Modified", type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date Added", type datetime}, {"Created", type datetime}, {"Modified", type datetime}, {"2022 Bookings", Currency.Type}, {"2021 Bookings", Currency.Type}, {"2020 Bookings", Currency.Type}, {"2019 Bookings", Currency.Type}, {"2018 Bookings", Currency.Type}, {"2023 Bookings", Currency.Type}}),
#"Added Year" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Date Added])),
#"Removed Blank Rows" = Table.SelectRows(#"Added Year", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Year", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",null,0,Replacer.ReplaceValue,{"Year"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"Year"}, {{"Total Customers", each Table.RowCount(_), Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Year", Order.Ascending}})
in
#"Sorted Rows"

 

Things that might be important to note?

- The extracted "Year" column is Whole Number as the type.

- There will be blanks because I dont have the "Date Added" (the date they became our customer) for all customers so the blanks should be the baseline number.

 

Thank you in advance for any help!!!

 

  • SOLVED!

     

    Added a column to the table on the Data View tab (on the left hand side of the screen). The formula is

    Cumulative Customers = CALCULATE(SUM('Cumulative Customers'[Total Customers]),FILTER('Cumulative Customers','Cumulative Customers'[Year]<=EARLIER('Cumulative Customers'[Year])))
     

     

7 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar

    Hi, in dax measures try this;

     

    Sumx(window

    (-1 abs,

    1 abs,

    allselected(tableYear), orderby(tableYear)), sum(Total Customers))

    • CierrahW's avatar
      CierrahW
      Regular Visitor

      The error says "Failed to resolve name 'Year'. It is not a valid table, variable, or function name."

  • hi CierrahW 

    not sure if fully get you, but you may try like:

    TotalCustomersAccu=
    SUMX(
        FILTER(
            ALL(TableName),
            TableName[Year]<=MAX(TableName[Year])
        ),
       TableName[TotalCustomers]
    )
    • CierrahW's avatar
      CierrahW
      Regular Visitor

      Below is the result. I need it to be cumulative each year not the current total showing for all years. Any ideas?

       

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User

        hi CierrahW 

        please depict your expected result properly and with necessary dataset.

  • CierrahW's avatar
    CierrahW
    Regular Visitor

    SOLVED!

     

    Added a column to the table on the Data View tab (on the left hand side of the screen). The formula is

    Cumulative Customers = CALCULATE(SUM('Cumulative Customers'[Total Customers]),FILTER('Cumulative Customers','Cumulative Customers'[Year]<=EARLIER('Cumulative Customers'[Year])))