RunningCount for product sold

[jayjay0306]

Hi people,

I hope you can help me with a challenge:

I have this datamodel:

from which I have made this table-example:

now, as it may appear here, what I need to make is a measure which calculate the following:

I need to show the development of number of customers buying a product.

The example shown is 5 customers and their daily order volume.

In this example "customer 1", "customer 3" and customer 5" buy the product for the first time in "week 1" (i.e. count=3), whereas "customer 2", "customer 4"  buy the product for the first time in week 2 (i.e. count=2), and together with the 3 customers in week 1 the total count in "week 2" = 5. 

What I need to calculate is the escalation in the number of customers buying the product. Hence, a table similar to the yellow table in the example.

My DAX problem is:

1) how do I "flag" a customer on his first buy and keep the flag for the rest of the period?

2) How do I make a runningCount on the number of customers who have bought the product (i.e. the yellow table in example)?

I have tried to make this DAX measure for "flag":

Flag = 
VAR CurrentCustomer = ‘Customer’[CUSTOMER_KEY]
VAR FirstSaleDate = 
    CALCULATE(
        MIN('F_Sale'[Date_key]),
        FILTER(
            'F_Sale',
            'F_Sale'[CUSTOMER_KEY] = CurrentCustomer && ‘F_Sale'[Sale] > 0
        )
    )
RETURN 
    IF('F_Sale'[Date_key]= FirstSaleDate, 1, 0)

but it only set Flag=1 on the date for the first buy. I need it to "keep" the flag=1 for all dates that follows the first buy?

I have also made this measure to count the customers:

Count customers =
CALCULATE(
    DISTINCTCOUNTNOBLANK([CUSTOMER_KEY]),
    FILTER(
        'Sale',
        [Sale] > 0 &&
        ‘Calendar’[Date]<=MAX(‘Calendar’[Date])
    )
)

    But it does not work properly.

Any ideas will be much appreciated.

thanks.

Br,

JayJay0306

[ValtteriN]

Hi,

For running total calculation you don't need to "flag" a value. Here is an example:

customer RT =

VAR MaxDate = MAX ( 'Calendar'[Date] )

RETURN

    CALCULATE (

        DISTINCTCOUNT('Table (38)'[Customerkey]),

        'Calendar'[Date] <= MaxDate,

        ALL ( 'Calendar' )            

    )

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[jayjay0306]

thanks ValtteriN, but - as I see it -  it doesn't take the logic "first date a customer register sale>0" into consideration? Where/how do I add this?

thanks again.

Br,

Jayjay0306