DAX - Cumulative count for only active customer

[mudria]

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.

[emudria3]

sample file.

https://1drv.ms/u/c/67b14179083490c3/Eb9FPxuFdeBDiqJeC1aAPFoBalmrMkkR1P01kzgm7mzsew?e=f82wgD

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[emudria3]

@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.

[emudria3]

sharing the link again. @Ashish_Mathur 

https://1drv.ms/u/c/67b14179083490c3/Edk6A_k7QhJJhHMKNwq9veoB_J-YpvjZUx6TcW1n_OONOg?e=RUxFpf

[emudria3]

@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.

[emudria3]

@Ashish_Mathur . Very guilty and thank you for the patience.

I have updated mu orignal post. 

The input data is daily dataset. Where purchases can be on daily basis. 

The output is a monthly number, showing total purchases, cumulative from the historical data - but for each month only including the customers which are present/active in that month.

Regards

[Ashish_Mathur]

PBI file attached.

Hope this helps.

[Ahmedx]

pls try this

[mudria]

@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?

[mudria]

I have added two new rows to same file in Feb.

Now, the total for Feb should be 7. It is showing me 12.

[Ahmedx]

you share the data, I understand you

[emudria3]

162.pbix

apologies. I thought I had attached it, but turns out my org didnt allow that. hopefully this link works. @Ahmedx 

[Ahmedx]

pls try

[emudria3]

Thanks @Ahmedx looks better but still not counting all.

Feb has got 5 values, so the answer for 5 should be these 5 plus 2 from Jan, ie 7.

I am getting 5 for Feb.

[Ahmedx · Accepted solution]

pls try

[emudria3]

@Ahmedx 
That worked 😍

Holidays were starting and I was stuck with this simple sounding complex for me challenge.

Thank you Thank you thank you 

 

Microsoft should thank you and rest like @Ashish_Mathur @ryan_mayu who are always there for the community.

Regards

emudria

[ryan_mayu]

what 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?