Create graph of sum of customers per year

[Anonymous]

Hi

I am new to power BI and are trying to create a graph that shows the sum of total customers per year.

Now I have created a measure that counts all customers and have a chart that display the increase per year:

I want it to be the sum. so line 1, line 2(1+2), line 3(1+2+3), line 4(1+2+3+4) and so on. 

How can I create this

Thanks for the help

[Tahreem24]

HI @Anonymous,

As per my understanding you want Cumulative count of the customer.

Running Total COLUMN =
CALCULATE (
    Count ( 'Table'[CustomerColumn] ),
    ALL ( 'Table' ),
    'Table'[Date] <= EARLIER ( 'Table'[Date] )
)

Please give Kudos to this effort and also accept this as a solution so other can take help from it! 

[Tahreem24 · Accepted solution]

Hi @Anonymous ,

You can calculate cumulative count of customer using Measure as well:

Running Total MEASURE = 
CALCULATE (
    Count ( 'Table'[CustomerColumn] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

 Please give Kudos to this effort and also accept this as a solution so other can take help from it! 

[Anonymous]

Hi @Tahreem24 

Thanks for the help!

Is there a way to add more filters to the query? I need it to be onlye active cusomers (status = 1)

I cannot get it to work

Thanks 

[Tahreem24]

Hi @Anonymous ,

Yes you can use AND function in filter like below:

Spoiler

Running Total MEASURE = 
CALCULATE (
    Count ( 'Table'[CustomerColumn] ),
    FILTER (
        (ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] )) && Status=1)
    )
)


OR Use the 2 different Filter function like below:

CALCULATE (
    SUM ( Table[amount] ),
    FILTER ( Table, Table[Column1] = ??? ),
    FILTER ( Table, Table[Column2] = ??? )
)

Please give Kudos to this effort!

[Anonymous]

Hi thanks for your reply

The last one wont work as a count guids that are strings.

In the first example I have this query:

Running Total Coumn = CALCULATE(COUNT(Contact[contactid]);FILTER(ALL(Contact);Contact[mg_customerstartdate] <= MAX (Contact[mg_customerstartdate])) && Contact[statuscode_display]="Aktiv")

I get this error message: 

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Do you know what is wrong?

Thanks