So we have compliance training yearly and instead of measuring how many people are doing the training daily or monthly or quarterly we want to measure how many people are doing it weekly say from Monday to Sunday. I’m not sure how practical it is. The training started for everyone on Monday August 29, 2022 and will end at 11:59 pm on Friday October 21st, 2022. Is there a way to capture this information Weekly?
2022 Compliance.pbix (3.1 MB)
Hi @lbarron20
I’d leverage Melissa’s date table here:
Extended Date Table (Power Query M function) - M Code Showcase - Enterprise DNA Forum
You can then join this to your data on the completion date column and use the week start or end date column from the date table as your axis to plot this weekly.
I’m having trouble following this query seems like way too much information to just get information from week to week.
That code generates a date table to enable you to do time intelligence functions such as the week to week information you’re after. There is a simpler one if you prefer:
Basic Date Table (Power Query M function) - M Code Showcase - Enterprise DNA Forum
I highly recommend you having a date table of some description in your models to allow you to do this kind of work much easier.
Optionally you can remove the columns you don’t need after it is generated.
Ok I have made an update and it has a category called Dates with functions of DayinWeek, Week Number, Week Ending. Day of Week Name. In the X-avis trying to figure out which of those would I need to use? Maybe I am missing something.
2022 Compliance.pbix (3.5 MB)
You need to change the data type of the Learning Plan Completed Date column in 2022 Compliance Learning Plan dataset to date and create a relationship between this and your Date table on the Date column.
It’d be the week ending column you need most likely, as the week number one is likely not to mean much.
Sorry I was in a conference the past few days. So I have changed the Date Column to be Date instead of Date/Time and then joined to the Date Table. Not sure why it isn’t doing what I need it to do.
2022 Compliance.pbix (3.7 MB)
Apologies I should’ve been clearer, you need to change the datatype in Power Query, I’m unable to do that to test and send to you as I don’t have access to the data behind. The relationship between the 2 is there, that’s fine.
Once this is done, if you plot the WeekEnding column from the Dates table as your X-axis you should get what you’re looking for.
Awesome I think this is it!