Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
@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
There is some problem on that page. It is not loading.
@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
You are welcome. Please review your latest post thoroughly for all relevant facts. Explain the question very clearly and show the expected result.
@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
@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.
I don’t understand, can you show what you are doing and what data you have?
I have added two new rows to same file in Feb.
Now, the total for Feb should be 7. It is showing me 12.
you share the data, I understand you
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
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
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?
Proud to be a Super User!
User | Count |
---|---|
92 | |
86 | |
66 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |