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,
I have a table where I have several thousands of bookings with a columns for the start date the booking and by what user. I have created a table with the date field (date hiearchy) and also the user field such as below. If I put the total on, it gives me a count of all unique users for the period.
As I am looking to report the monthly average of the below, I need to work out the sum of the total for each month (4189) and then divide this by the amount of months, in this case 12 giving me 349.08. I will be importing reports with different date periods. I've seen a few examples but getting stuck ....
Year | Month | Count of Unique Users |
2018 | January | 258 |
2018 | February | 457 |
2018 | March | 565 |
2018 | April | 152 |
2018 | May | 215 |
2018 | June | 321 |
2018 | July | 154 |
2018 | August | 568 |
2018 | September | 361 |
2018 | October | 859 |
2018 | November | 45 |
2018 | December | 234 |
Any guidance or assistance appreciated.
Hi @NiugeS
Have you solved your problem? If yes, kindly mark the answer helpful as the solution, so that the others having same question can find this post quickly.
Otherwise, please provide more details about your problem so that we can work on it further.
Thanks.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @NiugeS
Add a column with the year/month something similar to:
YearMonth = FORMAT(Table[Start Date], "yyyymm)
Now do the following measure:
Average = divide(DistinctCount(Table[UserID), DistinctCount(Table[YearMonth]))
should give expected result, but this depends also on the context.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Hi Miguel - apologies for the delayed response and thank you for the assistance. I have been trying your suggestion but I do not believe it is providing the desired result. It is providing an average of unique users accros the entire period.
In each month I could have (for example) 50 unique users. If these same users were to book each month, then the equation would result in 50 unique users / 12.
What I am looking for is each month's unique users to be added together to work out a monthly unique user count. Example, 50 unique users who book every month would be 600/12 = 50.
Hope that makes sense.
I am looking at something slighly different.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
85 | |
65 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |