Cumulative Sum

[SammyNed]

Hi team,

I could really use some help. i have a data table similar to that below basically showing Customer ID's and Dates that they placed an Order. I want to create a graph as below that basically shows the sum of the Loyal Customers over a time period. A Loyal Customer is someone who orders more than twice.

So in March we should only have 1 loyal customer (customer A with 3 orders).

In April we should have 2 loyal customers (customer A with a total of 4 orders and customer C with a total of 3 orders).

I have tried  the following DAX but it isnt cummulative:

Loyal Customers = var _CustomerOrders =

SUMMARIZE( orders, orders[cst_id],  "How Many Orders",  COUNTROWS(VALUES(orders[cst_id])))

return

COUNTROWS(FILTER( _CustomerOrders, [How Many Orders]>2) )

Please help

[Jihwan_Kim · Accepted solution]

Loyal Customers count =
VAR _currentmonthyear =
MAX ( Dates[End of Month] )
VAR _untilcurrentmonthyeartable =
FILTER ( ALL ( Sales ), Sales[Order Date] <= _currentmonthyear )
VAR _customerscounttable =
GROUPBY (
_untilcurrentmonthyeartable,
Sales[Customer ID],
"@customerscount", SUMX ( CURRENTGROUP (), 1 )
)
VAR _loyalcustomerstable =
FILTER ( _customerscounttable, [@customerscount] > 2 )
RETURN
COUNTROWS ( _loyalcustomerstable )

Link to the PBIX file 

[SammyNed]

Hi @Jihwan_Kim 

Thank you for your help in the first problem.

I was hoping you could help me adjust the Loyalty value. So instead of a fixed 2 orders making a loyal customer, i want to put a slicer on the page where the user can decide what constitutes a loyal customer, and then the slicer value can be used in the measure you described above.

So if the user chooses 7, then a loyal customer will be anyone with more than 7 orders.

The slicer should range from 1 to the maximum amount of orders by 1 single customer.

I created a table to summarize the Sales tables by ID and Count of ID (which is the number or orders).

Is this possible?

Thanks in advance

Sammy_Ned

[Jihwan_Kim]

Hi,
In my opinion, I think you can create a slicer by using "New Parameter" UI.

Then, in the measure, instead of 2, replace it with "selectedvalue( newparameter slicer value)".

Sorry that I could not find the sample pbix file, so I only could write like the above.

Thanks.

[SammyNed]

Works perfectly.

Thanks Jihwan_Kim