Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Greetings all,
I am pretty new to Power BI, and I was looking for assistance with a solution we developed for a client. They were looking for a way to track Exchange meeting categories for employee professional development. Basically, they want visualizations and data about how many of their meetings were categorized in a certain way. I achieved this by importing data from Exchange including the Category column, and creating a Duration column by subtracting the End from the Start.
If it matters, I also added two measures, one for a sum of the categorized meetings, and one for that sum to be subtracted from the number of hours in a work week or month, so that they could compare time spent in those categories versus time not spent in those categories.
The client is additionally looking for a way to track this data longitudinally. Keep in mind, this needs to be a relatively simple solution since we are asking their employees to do it for themselves, but this is a pbix file that they will refresh and re-filter each month, so I'm not sure of the best way to be able to track and show that data.
Including a screenshot of the sample visualizations and the Power Query table, filtered to show just this month. Please let me know if I need to provide more information - as I said, I'm new to this - and I appreciate any assistance in advance!
Solved! Go to Solution.
Hi @lshelton ,
Based on your description, you need to create a disconnected calendar table as a slicer, and then create a measure to show the differences of the previous months.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lshelton ,
Based on your description, you need to create a disconnected calendar table as a slicer, and then create a measure to show the differences of the previous months.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
OMG after some extra tweaking and with your sample file, I finally got what I was looking for! Thank you so much, I couldn't have done it without you!
Being able to look at data from January, February, March etc. even though in general this is a dashboard that is getting updated with just that month or week's data. They want to be able to see growth or change month by month.
My suggestion would be (instead of doing all of the work in Power Query) to bring the raw/detail/transactional data all the way into the Power BI model and do the calculations/groupings with measures. Since each "transaction" should have a date on it, you can connect to a calendar table and show values plotted against the month/year.
Hope this helps
David
Yes, each calendar event has a date attached to it, I was just filtering those dates in Power Query. I sort of understand what you are talking about, but haven't touched calendar tables. Would you by any chance have a link to a walkthrough you like for calendar tables to get me pointed in the right direction?
Hmm it doesn't look like a date table would work because there are more than one entry per day sometimes and some days don't have any entries.
Hi @lshelton - the Date table / Calendar table is designed for exactly that case. The calendar has all of the dates, and it joins to a key date table on your fact/detail as a Many-to-1 relationship, so it's ok if there are gaps in the detail. You then use the data from the Date table as your longitudinal axis and it automatically filters the detail.
Take a look at this blog post about calendar tables: Time Intelligence Calendar Table
David
Thank you, I will give this a try and see how it goes! Appreciate the guidance!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |