Forum Discussion
Calculated Column - Display BST times for summer days only
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!
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! :smileyhappy:
Cheers!
-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! :smileyhappy:.
-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.