Forum Discussion
DAX - Cumulative count for only active customer
Hi Community,
I need some help for a measure for cummulitive count over months - but only for the active customer in each month.
say,
Customer A purchased in Jan, Feb and March, it will be counted in Jan,Feb and March for cumulative.
Customer B purchased in Jan and Feb only. It will be counted in Jan and Feb only - not in March.
Customer C comes in Feb and March, hence counted in Feb and March.
March should have count from Customer A ( Jan, Feb, March) + Customer C ( Feb and March).
I have tried usual cumulateive which gives me incorrect number ie 9 for March where is should be 7.
cumulative = CALCULATE([count_of_customers],FILTER(ALLSELECTED(Date_Table),Date_Table[Year Month Number]<=MAX(Date_Table[Year Month Number])))
Sample file attached in below post - Thanks to Ahmedx for the file.
Any help will be highly appreciated.
Regards
emudria.
pls try
26 Replies
- Ahmedx
Super User
pls try this
Ahmedx Thanks so much. It is working as intended.
I missed one little detail, which is breaking my values.
My data is present at date level and I need to display the output at month level.
If I use your measure, and display my data at day level it works fine. But when I change that to month, it doesnot work.
How can use input data at dates, and output at months.- Ahmedx
I don’t understand, can you show what you are doing and what data you have?
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
Ashish_Mathur Thank you so much for your time and effort.
I have added two new lines in Feb data, the numbers are not adding up.
Since the purchases can happen on a daily basis, Now Feb has got 5 transactions, Feb should be should 7 ( 5 from Feb and 2 from Jan since both customers purchased in Feb)
file also attached
https://1drv.ms/u/c/67b14179083490c3/EaUmKZFVFLpCjfLuPRSJpOEBZ_8pKvg2HES9oif7S_80lA?e=cfSwJL
Since the purchases can be happening on a daily basis, The Feb isn this
- Ashish_Mathur
You are welcome. Please review your latest post thoroughly for all relevant facts. Explain the question very clearly and show the expected result.
Ashish_MathurThanks for the changed solution and apologies for being a pain. It is working on the data but not generalising.
I added couple new rows and it is not giving intended results. April should have 4 instead of 3.
https://1drv.ms/u/c/67b14179083490c3/Edk6A_k7QhJJhHMKNwq9veoB_J-YpvjZUx6TcW1n_OONOg?e=RUxFpf
Regards
emudria
- Ashish_Mathur
There is some problem on that page. It is not loading.
ryan_mayuwhat if customer B purchase in Apr and May again? so B is 1 Jan, 2 in Feb, 0 in Mar, 1 in APR and 2 in May?
Anonymous
Ideally it should be 1 in Jan, 2 in Feb, 2 in Mar, 3 in Apr, 4 in May.
Looking at any month, Ideally, we only count the customers which have a purchase in that month to get to the cumulative number.
- ryan_mayu
this is against your first post.
B didn't appear in Mar and you didn't count B in Mar as well. the 5 is from 3 A and 2 C.
not sure if i understand this clearly ,maybe you can try this
