Calculated Column - Display BST times for summer days only

[Ronie]

Hi, 
I'm pretty new to Power BI and I'm still struggling with its logic which I find quite different from excel. Any help would be great!

Context
I'm using Power BI to create a dashboard for my boss, displaying my team performance on a daily basis: handled call volume (by day, shift, individual), average waiting and handling times, etc.

For this, I'm pulling a .csv from our in-house tool and stick it into my model (change the query source for my 'Yearly' Table) I get the results I want but as my company is japanese, the .csv always shows time in either JST or UTC. As I work in London times are off during the summer time, as Japan doesn't have daylight saving time.

Objective
I'd like to create a calculated column in my 'Yearly' table which displays the time as GMT for GMT dates and BST for BST dates. 

BST starts on the last Sunday of August and ends on the Last Sunday of October each year. My first thought was to add a column to my 'Date' table and for each day of each year specify whether it's a GMT or a BST day. Then add +1 in the 'Yearly' table if it's a BST day. Unfortunately, I have no idea on how to automate that with a DAX formula.

That said, this might not be the right way to go about this.

Other Details
I'm using Frederik Vandeputte Date table and the headers of my 'Yearly' table are as follow:
User Name, Ticket ID, Queue In Date, Lock date/time, Unlock Date/Time

Again, thank you very much for your help, 

-Ronie

[jahida · Accepted solution]

Hi Ronnie,

Try this:

First, add this column to your date table:

IsBST = 'Table 2'[Date] >= MAXX(FILTER('Table 2', 'Table 2'[Month Number] = 8 && 'Table 2'[DayofWeekNumber] = 1 && 'Table 2'[Year] = EARLIER('Table 2'[Year])), 'Table 2'[Date]) &&
		'Table 2'[Date] < MAXX(FILTER('Table 2', 'Table 2'[Month Number] = 10 && 'Table 2'[DayofWeekNumber] = 1 && 'Table 2'[Year] = EARLIER('Table 2'[Year])), 'Table 2'[Date])

That formula assumes that BST includes the last Sunday in August but not the last Sunday in October. Not sure if that's right or not, you can tweak the inequalities near the beginning of each line as needed. Then, you can use that to adjust the times in your other table.

If you have relationships, you can use something like:

DatetimeAdjusted = IF(RELATED('Table2'[IsBST]), Table1[Datetime] + 1/24, Table1[Datetime])

If you don't have relationships (also going to assume you don't have a column with only the date), try instead:

DatetimeAdjusted2 = IF(LOOKUPVALUE('Table 2'[IsBST], 'Table 2'[Date], DATE(YEAR(Table1[Datetime]), MONTH(Table1[Datetime]), DAY(Table1[Datetime]))),
		Table1[Datetime] + 1/24, Table1[Datetime])

Hope that helps, let me know how it goes!

[Ronie]

Hello Jahida, 
Thank you so much for taking the time to reply. I really appreciate your help!

I just saw your post as I'm a bit busy at work but I will try your suggestion as soon as I've got the chance! 

Cheers!

-Ronie

[Ronie]

I just checked it and it works perfectly!

Thank you so much!

I might pick your brain again later, as I've got another issue related to shift patterns. I will try by myself first though, your solution gave me some ideas! .

-Ronie

Note:
I realised I made a mistake. BST starts the last Sunday of March not August. Silly me!
I thought I would mention it, just in case you thought it was strange. I replaced 8 by 3 in the solution you gave me and it works fine.