Total Customers By Year

[CierrahW]

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!!!

[CierrahW · Accepted solution]

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])))

[FreemanZ]

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]

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

[FreemanZ]

hi @CierrahW 

please depict your expected result properly and with necessary dataset.

[CierrahW]

This query below allowed me to make this graph as it produced the table in my initial post. Which is almost correct. 

let
Source = Excel.Workbook(deleted for privacy), null, true),
#"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"

However, it should look like this: Keeping in mind the baseline number is the value for the Year 0. Then every year the number should be inclusive of the count of the previous years. The goal is for the chart to show how many total customers there were in each year not just new ones. See below.

[olgad]

Hi, in dax measures try this;

Sumx(window

(-1 abs,

1 abs,

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

[CierrahW]

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