How to Make a Calendar Dashboard in Power BI

now consider this data for instance we have a date we have a marketing task that we are doing and how much money are we spending on that task and this data runs throughout the entire year of 2017. in case I want to present this data on the calendar this is how I'm going to use a native visual for power bi and put that out in a calendar and it looks like a calendar take a look so we have all the months right here and we have all the events presented right here now if I just switch over to any other particular month so let's say for February for Jan and for March for April I can take a look what date of that month I am doing what event and this is absolutely brilliant well in this video I'm going to show you how can we make a calendar like this in power bi now further Ado let's go let's just first take a look at how this visual works and then we will proceed on to the workings of this Visual and then break them apart step by step so we have a month slicer on the top that gives me the ability to take a look at any month of the year 2017 Jan Feb March and the calendar changes and I have also listed down all the events that we are doing on the right in the slicer should I want I can select any event and that event only is going to be highlighted I even have the capability to select multiple events in case I want to do that and that's nice about it now to be able to build this calendar we would need three things to put this together the first thing is the calendar itself which is this particular Visual and how do you form a calendar that's part number one the second thing are the calculations which are going inside of the calendar the display of what date is it the display of all the events on that day and how much money that you spent on that day now I have used the events and the money should you want once you understand the calculator relation you can customize the calculation to display anything that you want to display in your calendar and finally we would like to talk about the formatting as to how have I made this look a certain way so that it looks and feels like a calendar all right the three parts one two three let's go all it is to start with how do I make a calendar okay let's just start with the data model you can see that I have the events table which had a couple of columns right here and I have a calendar table linked with that now this is a very generic calendar table I'm sure you can take a look at the code let me just show you that so here is the calendar table and here is the code for the calendar table at the moment I have fixated the calendar table to start with 2017 year and end on 2017 here should you want you can obviously change that you can also use power query to create a calendar table that's not a mandate that you have to use tax but here are a few columns that I have created the dates the index the month the year the week the day name the day of the year which is 1 through 365 and then I have of the day number as today number of the week one through seven and here in this calendar I have said that my week is going to start with Monday and those are the two little inputs that describe that the day is going to start on Monday once you have the calendar or the calendar table and the calendar tables is linked what you can do is you can go over and start to make like a matrix Visual and convert that into a calendar format so if you take a look at this Matrix visual this is the visual that I have used and what I have done is I have used the week in the rows which are right here this is coming from the calendar table and the day name in The Columns as right here and that just gives me some form of cross-stabulated data representation that is not looking like a calendar as of yet but it's going to form a calendar I've also made a slicer on the month name right here and this actually does nothing at the moment it just changes the week number right here and that is our calendar at the moment all all right part number two is the calculations once we have taken a look that uh once our calendar or the Matrix visual is ready in the crosstab format uh you have week and you have the day name right here how can we put some calculations inside of this calendar to be able to do that what I have done is on every single cell I would like to present three things the first thing is that I would like to mention what day is that that's day number one day number two day of the month what day number is that that is one once you have the day number the next thing that I would like to present so let's just say this is day number one then the next thing that I would like to present is all the list of the events so I will just write events and then once you have the events I would also want to present that how much money did we spend in that particular day those are the three things that I'd like to present to be able to do that I have already built a calculation and I can quickly show you that all right the name of my calculation is events which is a measure and I'm actually concatenating three things I'm concatenating what day are we on at the moment this is my current day then a couple of enters right here then we have the list of events being concatenated which is using concatenate X I have done a video on concatenate X on creating some interesting context transition calculations this is one such example of that I will highly recommend that you please take a look at the video in case you do not understand creative ways of working with concatenate X nevertheless so the first concatenation is current Day events and then finally we have total spent all of this being concatenated forms a single calculation that I'm going to put inside of my visual right here so let's just drag the events onto our Visual and it looks something like this you have let's say the day right here what events did you do on that day and how much money did you spend on that particular day looks pretty nice now at the moment it's not really formatted so the next step is going to be the formatting part of it now two things that I'd like to kind of talk about before we move on to the formatting thing number one is that we do not really need the totals right here nor do we need the totals right here so I'm gonna quickly get rid of that so select your visual ride here I am going to go over to the format in the format I'm going to go to subtotals and columns and I can just get rid of that well I could also get rid of that right here but that's one the next thing is that there are going to be weeks or days when there is going to be no event now in that scenario I still want to present the day so let's just say that if you have no event on any of the Tuesdays I do not really want the entire Tuesday to disappear from my visual I still want to present that or if you do not really have any event on the 41st week all of this is blank then I do not really want the week 41 to disappear from my visual so I still want to present that so what you can do is you can go over to the data and say that hey just right click here and even show items with no data that's the option that I check for the week and similarly right click on the day name and I'll say hey show items with no data and that is going to make sure that all the days and all the weeks are presented no matter they have the data or not once you've done that let's just move over to formatting all right in terms of formatting we have the current Visual and I have beefed up the look and feel of the visual I'm sure you can do that it's just laying out the table in a nice structure giving enough padding and things like that changing the font changing the color and it's going to look like that I've also changed the look and feel of my month slicer and that is now sitting on top of this looking pretty much synced with my visual now at the moment there are two key things that I'd like to talk about in the formatting the first one is how are we coloring the events right here on the days when you have the events happening and the second thing that I'd like to talk about is that the slicer that was there initially when I showed you the visual in case I select any particular event then only that event should be highlighted and the rest ones should not be highlighted how can we do all of that let's just start now to be able to build a slicer right here we would need a table and on top of that table we will create a slicer so to be able to do that what I have done is I have created a disconnected table and this particular disconnected table is using a very very simple tax calculation which is saying hey just give me one single column which is going to be the events and the task name which is right here and give me that column as a unique values in this particular table using the All no blank row function now if I just go and take a look at this particular table which is disconnected I have the list of all of the events that happened in my entire data using this column I'm going to make a slicer on my page so you can see that I'm just gonna maybe insert a slicer right click and add a visual and I'm going to say hey I want to make a Slicer in the slicer I am going to maybe add the task right here and here are all the tasks the next thing that I would like to do is I would like to connect the selection of any one of these tasks one or more tasks and I'd like to similarly highlight those tasks on my calendar visual which is going to be a very simple conditional formatting measure let's just take a look at the measure that I have made right here so if I just go over to the conditional formatting measure all that I'm saying is that hey why don't you take look at this particular table which is right here and see how many events are highlighted then I say that hey please take a look at the events which are coming from the events table and take a look at what event is being done on that particular day so once you take a look at these two please tell me which one of the events is happening and if the count of the number of events is more than one then you apply this particular color very simple tax calculation nothing that complicated now once you have applied the color this color is going to be used as a conditional formatting on this table I can select that table go over to the format more options and I can just go over to the cell elements and activate the background color highlighting so I just go over to the FX right here and I'm going to say that the formatting conditional formatting is going to happen based on the field value and the measure that I have written is conditional formatting measure the one that I just showed it to you you do that click on OK and it looks something like this and it works beautifully so in case I would like to take a look at ATL activities and maybe webinars I'm just taking a look at ETL activities and webinars and it looks gorgeous all right that was all about the calendar visualization in power bi using some native visuals which is nothing but the Matrix visual obviously if you would want you can customize this visual to any extent once you have made the standard calendar visual add some ancillary visuals along with that and your dashboard is pretty much ready let me know if you have any questions around this and I'll be glad to reply in the end I'd like to give a big shout out about my tax and my power query training courses in case you are a beginner in power bi and you'd like to level up your skills learn the fundamentals first and then move on to solving harder problems of your data I would highly recommend that you take a look at my courses it is going to be super duper awesome thanks so much for taking all around and I'll catch you guys in the next video bye now [Music] thank you

Channel: Goodly

Views: 41,080

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: OTiRqJKl-2w

Length: 10min 47sec (647 seconds)

Published: Wed Sep 06 2023