Model was launched later on and later on and later on and how do you compare those to all of them and get a clearer picture. Well you can do that using a since day 0 style graph just like this one and the same data so the same confusing data if laid out in that since day 0 graph in Power BI actually looks like this. You can see how beautiful it is and how clearly it's conveying the.
Picture. I mean you can clearly see you know where the winners are and the folks who are in the middle of packs and the folks who are not really pulling their weight. So that's the beauty off the since day 0 graph but it's wait wait there's more now this pattern of course you can use it for showing product sales since the launch day but this is a very generic pattern of basically showing a.
Measure since a start date and you can find applications for this and pretty much any industry or any data you're working for .I'll just show you some examples so for example in marketing you might have campaigns that have different start dates again the same challenge they have different start dates but you want to compare their performance since the start off the campaign you can do.
That using this since day 0 style graph let's talk about human resources different employees may have different join dates but you might want to show the since the date they joined and you might want to track their vacation ours or training ours or something else since they're joined it and you can do that too using this style of graph let's say.
Power BI author and if you want to become a Power BI pro make sure to subscribe and yes click that bell so you are notified whenever I go live to answer your power bi questions now if you're getting started or keep getting stuck in power bi then make sure to check out our number one-ranked power bi tutorial and we're gonna put a link to that in the corner and down in the.
Description so let's move on and build our since day zero graph so let's start with the data that looks like this and an end we want to get something that looks like this the cumulative totals in the since day zero style pattern that we talked about now of course but you can build something like this and then if you like extend it to build automation so you can see so we're not going to.
Cover this in this video but you can certainly build automation on top of that okay so let's get back in a file and get started so we're gonna do this in three steps ABC and by the way this was inspired by a question that was asked by Diego Lopez a part of a learn part be a family in one of our earlier talk power bi episode so Diego thank you for asking this question so let me just.
Give you a lay of the land so here we have sales data and then the classic data lookup table schema we have location product and the calendar table now you can see that if we show this by calendar table it looks very messy so what we really need is a different kind of calendar table calendar since launch table and we're gonna build that in our transform.
Data so let's open up our query editor and we're gonna build it over there so this is gonna be or step eight right here so I'm gonna add a special calendar table so I have my transform data screen up if I can find it there we go and what I'm gonna do here is just say new blank query I'm going to change its name and I have the query for this laid out already and I'm just gonna base this.
Posts Related:
- Introduction to Microsoft Dataverse in Power Apps Build Tables & Relationships Beginners Guide
- Membuat Logo Tiga Dimensi Metalik dengan PowerPoint PowerPoint
- Este Microsoft Excel ! Sistema de painéis de estatÃsticas financeiras Tutorial 1.
In the advanced editor by the way I'm not gonna go into this in more detail - ID Card Make
Because this essentially is very very similar to the pattern that we have for our ultimate calendar table so this special table has been built following the same pattern and by the way if you haven't seen that you must watch that video this is perhaps the most important table in any part of your model the.Calendar table and you and we're gonna put a links in the corner and in the description down below where you can watch this video and download the ultimate calendar table for free so just to give an idea so again this is a special calendar table this does not have dates this has days since launch because that's what we are interested in right so we're saying you know after day.
One day two and day three now of course being a calendar table it can also group these days into months or quarters so if you want to see things at a different granularity but again so kind of like a calendar table is slightly special so we have this table now and now we got to do the next step which is we gotta add something in the sales table which will let us link it with this stable so again.
The key for this table is not dates it is days since launched so we need pretty much the same column in our sales table so we're gonna go here and build that here and for this the first thing I'm gonna do is I have noticed a product ID key and in my product table I have the Product ID key but I also have a launch date so I'm gonna grab that first I'm gonna go in my sales table and I'll say.
Merge queries merge it with a product table on product II hit okay and expand this and the only column that I'm interested in is in launch date so let me get that and now I can add a custom column days since launch and here I can simply say purchase date - the launch date and it's as easy as that and this is the part.
That I love about the kitchen of power bi the query editor and that it has a trash can if you have no idea what I'm talking about go check out my 60 minute tutorial that I referenced earlier cool so I can get rid of the column after I've used it and for this one I need to change it to a whole number and we're ready to go so I'm gonna load this data and be right back alright my data has.
Been loaded and the relationship was detected automatically as well if it isn't then it's a very simple relationship just these are connected in a one-to-many relationship using the days since launch column so let's see what we can do with this so we had this table that we were starting with it again kind of really messy and and the biggest challenge was that these and.
These models were launched later on in the year so how it's you know you can't really compare them side-by-side so I'm gonna grab this graph and put that in here and instead of the the calendar table which is what I'm using right now I'm gonna switch that field to use the calendar since launched instead so actually didn't want to do that remove the month here and instead of.
That I'm gonna put the month number on the axis but this is a special month number this is month number since launched and well this is probably not what you expected now trust me this is still progress now if you notice what has happened here it has lined up everything on this side so in the earlier graph this is the before.
There was stuff that was starting at different dates right I mean August and
2018 and so forth so what this has done is it has lined up everything like Oh everything starts at day zero but this is still not the same because we only need to do one more step which is instead of showing the actual sales for that we just need to show cumulative sales so that's the only measure we need.To add and we're gonna do that as our last step here all right so let's go to the sales table go to modeling and I'm gonna create a new measure now I'm just going to base this formula in now for those of you who are part of my learn power bi program you would recognize that this is the the same exact formula that we cover inside the program in your time intelligence measures when we are.
Covering monthly date quartered according to the year to date and then life to date measures time intelligence measures and this is pretty much the life to date pattern by the way folks if you are interested in our training programs then just could just head to our website learn power bi comm then under training you can find or learn power bi course and join the wait list.
To be notified as soon as it opens okay so now that we have the cumulative measure I'm gonna copy this graph put that in our next step and all I need to do is swap out that measure someone has removed the sales quantity and instead add the cumulative sales quantity and there you go again the the since day zero style of graph for or business data and as I said earlier this is a very.
Flexible pattern can be applied in a lot of different scenarios and in pretty much any industry now I had mentioned the play axis that is a little bit advanced we would not be able to cover this in this video in fact this one is covered in our advanced power bi program so if you go back to our site and look under training look for that is our advanced power bi training.
Program and you can find information about that on our site if you want to download this file then you can head to this link we're also gonna put this link in the corner and down in the description below this video was created based on a question asked by Diego Lopez in our monthly talk bar be a Q&A show you can find more information on submitting questions and how to join.
