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...
  • CierrahW's avatar
    CierrahW
    3 years ago

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

     

CierrahW's avatar
CierrahW
Regular Visitor
3 years ago

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
3 years ago

hi CierrahW 

please depict your expected result properly and with necessary dataset.

  • CierrahW's avatar
    CierrahW
    Regular Visitor
    3 years ago

    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.