Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rayinOz
Helper III
Helper III

Calculate the month-over-month of Course Completion

Hello,

 

I'm looking to calculate Month over Month course completion rates. For example, in January we had 67% complete on course ABC. In February we had a 80% completion rate. Either a bar or trend line.

 

Table name is 'UoM-TrainMe-Report'

 

Columns are [Enrolment Date] and [Course Name] and [NewCourseEnrolment]. The date format fro CSV file is DAY/MONTH/YEAR using numbers which PowerBi converts to Wednesday, 12 June 2017 format.

 

 

I also have three measures which are Total Enrol (which is a count of course enrolments), Total Completed (a count of course enrolments with a status of Completed) and Percent Completed (which uses the Total Enrol and Total Completed measures to calculate the percent complete)

 

I hope all this info helps with a solution. Thanks guys!!! Youve been a huge help!

 

RayinOz

1 ACCEPTED SOLUTION

So here are the steps:

 

- add continuous calendar table in your data model

- set relationship between your data table and continuous calendar table

- calculate cummulative measure

 

and you are good to go...

 

As a best practice, it is always nice to have continuous table for time intelligence analysis.

 

Thanks,

Parv



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

8 REPLIES 8
rayinOz
Helper III
Helper III

Here is an update...

 

I was able to show Month by Month... but it isn't cumulative... each month that is shown only shows completions FOR THAT MONTH. But what i need is cumulative.

 

Thoughts?

Month by Month.PNG

 

 

 

 

Few important items:

 

1. Do you have continuous calendar table in your dataset?

2. You have to calculate cummulative total measure to achieve this?

 

There are lot of articles on cummulative/running total, here is quick reference for you:

 

http://www.daxpatterns.com/cumulative-total/



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

ahhhh.. ok. No, I don't have a continuous calendar table setup in my dataset. I only have a date column in my primary table for Enrolment date.

 

i'll take a look through the link you provided.

So here are the steps:

 

- add continuous calendar table in your data model

- set relationship between your data table and continuous calendar table

- calculate cummulative measure

 

and you are good to go...

 

As a best practice, it is always nice to have continuous table for time intelligence analysis.

 

Thanks,

Parv



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

OK... Found a resource to walk me through creating a calendar table in powerbi....

 

https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-step...

 

now.. to set a relationship between the calendar table and my data table... i think the [enrolment date] is the date column i want to use from my data table.

Awesome, that is perfect. You see "DateAsInteger" in calendar table, you have to create similar column on your data table, let's "EnrolmentDateAsInteger", to do so, follow these steps,

 

- Go to Query Editor

- Select your data table

- Click Add Column

- It will open up a window to add formulat for custom field, give it a name "EnrolmentDateAsInteger" and then add this formula =Date.ToText([Your Enrolment Field, "yyyyMMdd")

 

If you Enrolment Field is date/time and then add this formula

 

=DateTime.ToText([Your Enrolment Field, "yyyyMMdd")

 

Once you have this field, it will show in your query and now we can use this field to set relation with calendar table. Give it a try and let me know if there is an issue.

 

Cheers,



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Fantastic! I've setup the relationship between the tables... next step is to setup the calculation for the cumulative measure....

 

you've been a tremendous help...  i'm almost there!

If need further help with calendar table, let me know or send me your sample pbix file and I can get back to you with solution.

 

Cheers



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors