NEVER GET THE WRONG TIME AGAIN / Switch Time Zones and Clock Changes in in your Power BI Reports

in this video we're going to look at how you can add last refresh dates within your power bi reports we're going to go through the basic steps of how you can add last refresh dates we're going to look at how you deal with the refresh dates in different time zones as well as how to handle things like when the clocks move forwards or backwards all of that and more so without further Ado let's get started hi my name is fanan and welcome to the resolution subroad YouTube channel or recovery tips tricks and best practices when working with power bi I upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so adding a last refresh date somewhere within your report page is a pretty typical way to give your users some insights on how fresh the data in your page is and it's a useful information for your users as well as for yourself if you have a report that is refreshing regularly so let me show you how you can easily do this in power bi so what you need to do in power bi first of all is you need to find what the current date or time is and there are two ways that you can do this the first thing is through Dax so here I created a simple and empty report and what I'm going to do is gonna click new measure here and we're going to say today Dax and we're gonna use the function today now there are two functions that you can use here today and UTC day today just simply gets the local date and time based on where I am locally and UTC is basically the time zone and is zero and just keep that in mind because we're gonna go back to that later so for now we're gonna get today and it creates us a Dax measure here which if we drag into our page here and create a card what you will see is it will just simply give me the date today which is the 15th of April now if you want to get the time as well you can use the counterpart Now function so you have now a new TC now which the nouns will also add the dates as well as the time within your measure so what you can see is now if I save this and I hit refresh for example you will see that those numbers will change because as the time passes the the time also changes here in our page now Watch What Happens um it's 1840 and 39 here in our time here and watch what happens when I reopen this report so I'll hit save and I'm gonna reopen that power bi report again just to show you what happens so what you'll see is now that this Dax function even though the data that we have theoretical data that we have in this report hasn't really changed or updated the time or the calculation has updated to get the current date and time which in the last refresh dates context you probably don't want to have because you want to only show the date and time when that data was last refreshed so using Dax or last refresh date is not a good idea it's a good idea if you want to calculate based on the current date and time but if you want to monitor the last refresh date you want to use and find what the dates and time is in power query let's go and create this in power query so I'm going to open a power query by hitting transform data right here and what we're going to do is I'm going to click new source and I'm going to open a blank query so what we're going to do here is we're going to type dates time dot local now which simply returns us the current date and time right now so you can see that it's it's just returned as a value here and let's convert this into a table I'm going to name this date and I'm gonna name this one last refresh I'm going to convert this into a date time column and that's pretty much how you update this or you create this last refresh date in power query so how would you use this in your page so if we hit close and apply now you saw the first way that you can add this into your report which is by adding it into a card but what I prefer to do is add it actually as text as a dynamic text just because I have more formatting options when it comes to adding texts before or after it so if I delete this one for example I'm gonna go insert text box and I'm going to drag this text box to the right here I'm going to type last refresh date and then from here I'm going to click value here and I'm going to get last refresh date from from our column here okay let's try again Max of last refresh sometimes it takes a while for it to to render so if I just do it like that Microsoft last refresh yeah if I hit OK so you'll see that it gives me both the dates and the time of the last refresh from our table now if I hit refresh now in this data set you'll see that that number updates but if I save this and reopen the file that number or that last refresh date will not change which is what we expect because we don't really want this to update unless the report is refreshed So within power bi desktop everything seems straightforward when you use date time local now it just gets the dates of your local machine and plugs it into your report page now this might not be the case if you're using the power bi service and that's because your tenant's geography might be different based on how it's configured so you might be living in the UK but your tenant might be situated in the US which is in a different time zone so what would typically happen and it doesn't happen to me because my tenant is in the same region as I am and what will happen typically with the last refresh date is in power bi desktop you will have the dates your current date and time but when you refresh that same data sets in the service when you publish your report you will have a different time and that's because the function date time local now gets the current dates based on the geography where this data is stored at so how would you know which geography your tenant is situated so let me show you I'm going to open up the power bi service here from the power bi service you can hit the help in supports and under about power bi you can see where your data is stored now if this is different to where your region is you need to deal with the time zone to show the last refresh date based on your region that you're at not where the data is stored so I'm going to show you a trick of how you can do this in power query so for this let's pretend that we are in the Philippines and our data is stored in the UK and Philippines is UTC Plus 8 which is here at the moment it's 1 46 am there and we want to make sure that our last refresh date always reflects the current local time not the time in the Azure so how do we deal with that so let's open up power query again here and let's go back to the last refresh table here and the first thing that we can do is first let's duplicate this dates table this date column sorry I'm going to name this date time zone and I'm going to change this type into a date time time zone and this is because we want to switch the the offsets of the dates or the date and time to what the local time zone would be so and the first way to do it is by converting or creating a date time zone column type so the next thing is I'm going to create a new custom column here and we're going to use this function called Date timezone dot switch Zone which accepts two values so the first value is the date time zone data that we have so it's the first the the column that we've just converted and in the second parameter that asks for is the time zone difference so we want to make it so that it goes up by by eight which is what the time zone is in the Philippines and the other one I believe is optional so when I just close it like this and I'm gonna just say time in Philippines like this and if you hit OK so you'll see that that time now corresponds to the local time in the Philippines so you simply just convert that into a date time and there you go so that would be the same as what you get here when you which is the current time locally in the Philippines so now that you know how you can easily switch your time zones to different parts of the world now let's look at something a little bit more complicated not all of the countries do this but basically the majority of the European countries move their clocks either forwards or backwards when the summer time finishes and when the summer times ends so in the UK for example during the summer time which starts on the last Sunday of March the clocks move forward so where we typically will be UTC zero it will be plus one whereas when we reach the last Sunday of October the clocks move backwards so it will go back to UTC zero and so we need to kind of account for for this difference to the conversion whenever the clocks move forward or backward so how do we make sure that our calculation or our logic honors this change in a difference in the offsets when the clocks move forward or backward so let's do another scenario here so let's say our local time zone is France and which is at the moment it's a plus two because we are in summertime but typically it will be a UTC plus one time zone so we want to make sure that if it's at the moment we are in 2023 so and we are within summertime so if it's after on or after the 26th of March the offset needs to be two but if it's beyond this if it's after or or it's not within the summer time if it's after 29th of October it needs to be plus one which is a pretty simple thing to do um because considering you only need to make sure that the dynamic element updates the offsets from this function now what makes this a little bit extra complicated is that the date in which the time zone switch is based on the day of the week which is the last Sunday as opposed to a specific number like a 15 or 29 as you can see here so it changes based on which where the Sunday Falls so we need to do this and break it down into several steps so you can understand how we go about fixing this so let's start and let me just delete all of these steps for the Philippines because we don't really need those anymore and so so what we need to do first is we need to create two columns to check and get first what the starts and end of summer time is so let's create a new custom column here start of summer time and in this custom formula what we're going to do is we're first going to try to get what the date is and I'm going to use a string literal here to say get me 2023 and then let's just get me the 31st of March which is the last day of of March right if we hit OK so it returns us the date as we expect and now what we're going to do is we're going to wrap this with a date that starts a week which gives us a different date based on what date that we feed it in and it takes two arguments the second argument is which day should it start and we're going to give it Sunday as the starting date so what it will do is it will take that last week of March because we set it as the 31st of March and then it's gonna try to get what the last Sunday is based on this function so if I hit OK let's see what I misspelled here perhaps this yeah here we go so it gives us the 29th of January which I missed yeah that's that's January so I'm going to change that into March which will give us the 26th of March which is the same as when as we expect this is the last Sunday of March and the last thing that we need to do is we need to account for other dates apart from the current year at the moment we hard coded 2023 here but what happens when this report rolls over to 2024 for example so we want to make sure that this is dynamic so a simple way to fix that is we're gonna replace 2023 with a year function so dates that year and in here we're gonna just give it the date function oh the date column sorry so now that is dynamic based on the current year that you're at so now what we're going to do is we're gonna do we're gonna get the end of summer time which is the last Sunday of October so I'm gonna copy the uh the code that we've just created I'm going to create a new custom column I'm going to paste that end of summer time and we're going to change this to get the last day of October which will give us the last Sunday of that month and 29th of October is the same as what we have here perfect so now we are ready to add our last conditional column so now we need to get the change the offset or get the offset based on which dates we currently lie on are we within the summer time which makes the offset plus two if we are outside of summertime it would just be plus one so I'm gonna get the offset first so friends offset I'm going to say if the date is greater than or equals to the start date and the date is less than or equals to the summary so if we are within the summer time then to else one so let's see okay I see it's because we're I'm comparing a date time value into a date so what I'm gonna do is I'm gonna just change this into a date type like this and that should fix that because otherwise it's trying to compare two different data types so now it gives us two and that's because we are currently in 15th of April which is within the summer time so it needs to be a plus two so now that we've done all this prep work the last thing is now to just get the the time zone in France so France local time we're going to use the same function like before so dates time zone dot switch Zone we're gonna give it the date time zone column and then we're gonna give it the time zone in hours instead of writing two or one we just get the data from the friends offset column if you hit OK so you will see that now it gives us the current local dates or time in in France which at the moment it doesn't really give us the time because we have we don't have date time here so maybe what I will do is I will just change the date I'm gonna just convert everything to date time so that we can see the the time values on all of these columns so here I'm gonna change these into date time instead like this which now as you can see this is the current local time in France so if I just let's just search that quickly just to make sure Ryan's time is 1959 is almost 8 P.M which is uh there we go is is the same as what we have here and that's really it for this video I hope you're now a little bit more familiar with how you can use the last refresh time and how you can fix issues with it when you publish it in the power bi service thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so not to do better for next time ask your questions in the comment section box below so I can help you and you can help others if you really like this video we have a patreon page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye

Channel: Solutions Abroad

Views: 7,910

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, last refresh date, power bi timezone, power bi clock change, power bi utc, power bi local time, switch timezone

Id: Goysa4jw7Ms

Length: 17min 10sec (1030 seconds)

Published: Wed May 24 2023