cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## RunningCount for product sold

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

2 REPLIES 2
Super User

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!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.