Create Mark Date with airSlate SignNow

Create mark date

yo what's up it's Patrick from guy in the cube and in this video we're talking about dates stay tuned okay pick up where Adam left off so this video not something I you know got from a customer an idea just picking up for my homie right the other guy the other guy in the cube left off talking about dates he created this cool date table using a nice tax expression but now I want to use this date table I want to do some time based some temporal analysis how do you do it well you guys know right how I like to do this right so the first place I'm gonna start is where let's head over to my desktop okay so take a look at this report what I like to do is I'd like to compare the current year gross you know sales movie sales fooled by studio and so I'm looking at 2016 and I see that one of brothers had the most but my my calculations not working what's wrong with it right I'm just using saying P I'm just using the same period last year in the Dax expression why isn't it working well by default you guys know how it goes right if you know you got to do certain things to make those time-based calculations work when you bring in your own date table so I'm gonna show you a couple of ways but then I'm gonna show you the new feature the new feature that was released in the February 2000 update 2018 2018 update that just simplifies this whole thing alright back to my desktop so after I create my measure what do I do right so typically when if you're especially when you're working with the data warehouse what is your data team do right the guys that designed the data warehouse when they design out the model especially on the date table so I'm going to go over to my date table here they design a smart key and the smart key is usually there for performance purposes right it's better to join on an integer than a date and so but what happens is when I import this into the desktop so check it out import this into the desktop it automatically establishes that relationship between those two day keys and that just doesn't work in power bi desktop well it didn't work right it doesn't work and so one of the workarounds was to go and you know kind of if the date existed you can maybe convince your DBA or whoever your data warehouse person is to allow you to pull that in or maybe add it to the you know boat the fact table that you're joining to or whatever table you're joining to and the date table and maybe they'll make that modification you update it and pull it in if they don't then you'll have to write some M or some dacks to bring those in then you establish the relationship on those columns kind of like I do here right so I go here instead of using my smart date key I go and find the actual date which in my case is the oh where are you release date and then I join up on the actual date in my date table and I click OK and then I go over here to the visualization and it works so that's one workaround requires some steps requires some work but you guys know how I am not lazy just efficient and that's just not efficient enough my man Marco what's up Marco see you in London in a couple of weeks he said hey Patrick when I posted a video about this before I don't remember which one he hit me up and he's like hey Patrick go check out my blog and I'll give you the link to his blog I have an easier way to do it and so that way what Marco did was check this out he went over and he's got this markets date table so by using this approach I don't have to modify the dates I don't have to change relationships I can join up on those I can join on those smart keys and then I'm kind of you know behind the scenes marking it as a date table so let me show you what I'm talking about I'm gonna go back here to my relationship switch it back to my smart keys click okay and then add a relationship between the date in the markets date table and the actual date increase their relationship switch over now it works right still a little work still a little work to mean steel wow that's a little easier than what I did before steel too many steps I'm looking for the easy way out with this so what I'm gonna do I'm gonna go back here I'm gonna delete this relationship let's get this deleted BAM just to show you just to show used to make sure there's nothing up my sleeves right just to show you after that deletes we go back it doesn't work in the February 2018 update of the desktop guess what the product team do you mind blown you guys remember exhale right you use PowerPivot you remember the old power pivot stuff it's not old people still use it right in Excel there you had an option to mark his date table also if you use SSAS you had an option to mark as date table they finally brought it to the desktop check it out right so what you do is click on modeling in the ribbon select your date table all you need to do is Marcus date table and select your column so be sure to read this right so the column must be of the datatype date and it must contain a unique write must contain only unique values or distinct listing of dates just like the other requirements and SSAS in PowerPivot right so go ahead and choose that column date this is also going to get rid of all those hidden virtual tables that they create for you so I'm going to go ahead and click OK and now BAM so I click away and now it just absolutely works it's like magic oh right I love magic sometimes I when I was a little I said I was gonna be a magician no I wasn't alright what do you guys think I'm curious these two methods that I show these kind of workarounds what are you doing did you use them you have other approaches if you do and any other comments and questions post them in the comments below if this is your first time visiting the guy and a guy in a cube channel please be sure to subscribe if you like my video give it a thumbs up as always from Adam and Patrick thanks for watching and we will see you in the next video boom boom boom okey-dokey okie dokie win too