Hi,
First let me give some context to the problem.
I have a roster table which specifies the details about 12 hr rostered shifts. start time (in local and UTC), finish time, whether day shift/night shift and so on. The business views a completed day for reporting purposes as when the day shift AND the night shift completes. Note that the night shift finishes on the morning of the next day.
So i'm trying to create the calculated column syntax that can take the current time when the dataset refreshes (eg UTCNOW) and from that time value work out where you are in that roster table by comparing that time to the shift start and end dates.
Once i can do that successfully then it enables a few columns to be created for things like :
isListShift : identifies that last completed shift compared to dateset refresh time
isLastDay : identifies the last completed "Day" compared to dataset refresh time
isMTD : identifies the rows that contribute to MTD calculation*
* MTD : the nuance here is that on the first of the month the MTD is the prior month as you are currently in a day that hasn't completed yet. (I can't use the relative date filtering in the pbi service as that works on UTC and as AU is ahead in time, the new month starts on UTC Jul 31 20:00 and finishes on UTC Aug 1 8:00 so when it clicks over to the 1st the relative date filter of "is in this month" switches to the new month. It needs to b epinned to Aug and not switch to Sep until local Day 2 of the month starts.
I know this sounds like a christopher nolan time travel film....
I have somethign working with layered if statements but i'm sure there is a smarter pattern.
The table is used as a "date" table via relationship to fact tables.
A subset of the roster table is pasted below.
My current calculated column is also below.
isCurrentMonth =
// due to the powerbi service relative filtering only doing UTC, the relative filters for MTD don't work.
// this column identifies the current month for the purposes of filtering for MTD calcs. You use it in combination with isPriorDays
// the trick with this column is that when it is being run at a time that equates to Day 1 - the month to day is last month. Not unti you are in day 2 does MTD start back at 1
var UTCOffset=10/24
var CurrentTime=UTCNOW()+UTCOffset
// TEST MIDDLE OF YEAR
//var CurrentTime="8/25/2020 9:00:00 PM" + UTCOffset // middle of month
// var CurrentTime="8/31/2020 9:00:00 PM" + UTCOffset // day shift on first of the month
// var CurrentTime="8/31/2020 7:00:00 PM" + UTCOffset // day shift on first of the month
// var CurrentTime="9/1/2020 7:00:00 AM" + UTCOffset // day shift on first of the month
// var CurrentTime="9/1/2020 9:00:00 AM" + UTCOffset // night shift of the 1st sets
//var CurrentTime="9/2/2020 9:00:00 AM" + UTCOffset // next day
// TEST AROUND END OF YEAR
//var CurrentTime="12/25/2020 9:00:00 PM" + UTCOffset // middle of month Y
//var CurrentTime="12/31/2020 9:00:00 PM" + UTCOffset // day shift on first of the month - Y
//var CurrentTime="1/1/2021 7:00:00 AM" + UTCOffset // day shift on first of the month - WORKS
//var CurrentTime="1/1/2021 9:00:00 AM" + UTCOffset // night shift of the 1st sets WORKS
//var CurrentTime="1/5/2021 9:00:00 PM" + UTCOffset // middle of month Y
var CurrentMonth=Month(CurrentTime)
var CurrentYear=YEAR(CurrentTime)
var CurrentDay=day(CurrentTime)
var CompareNowtoDate=day(CurrentTime)=day('COP - Shift Roster'[Date])&&month(CurrentTime)=month('COP - Shift Roster'[Date])&&year('COP - Shift Roster'[Date])
// slightly different logic if its january
var compareFirstDaytoPriorMonth=if(month(CurrentTime)=1,
day(CurrentTime)=1 && month(CurrentTime)=month('COP - Shift Roster'[Date])-11 && year(CurrentTime)=year('COP - Shift Roster'[Date])+1,
day(CurrentTime)=1 && month(CurrentTime)=month('COP - Shift Roster'[Date])+1 && year(CurrentTime)=year('COP - Shift Roster'[Date])
)
return
// first handle where all rows in roster are the first of the month
if(day('COP - Shift Roster'[Date])=1,
// does the current shift day equal the first of the month, because if it does - it needs to be set to 0 as MTD when on the 1st is the full prior month
if(day(CurrentTime)=1 && month(CurrentTime)=month('COP - Shift Roster'[Date]) && year(CurrentTime)=year('COP - Shift Roster'[Date]),
"0",
// if current shift day is 1 but its the prior month then need to set to 1
if(compareFirstDaytoPriorMonth,
"1",
// if current time is not on the 1st of the month, then its fine for the 1st to be set to 1
if(day(CurrentTime)<>1 && month(CurrentTime)=month('COP - Shift Roster'[Date]) && year(CurrentTime)=year('COP - Shift Roster'[Date]),
"1",
"0"
)
)
),
// else what to do for all other days [Date] = 2 to end of month
if(compareFirstDaytoPriorMonth,
// want to set to 0 as when its the first 2 shifts of the month the MTD is the prior month
"1",
// if current time is not on the 1st of the month, then its fine for the 1st to be set to 1
if(day(CurrentTime)<>1 && month(CurrentTime)=month('COP - Shift Roster'[Date]) && year(CurrentTime)=year('COP - Shift Roster'[Date]),
"1",
"0"
)
)
)
| id | ShiftIndex | Date | DateCode | Shift | UTC_Shift_Start | Shift_Start | UTC_Shift_End | Shift_End |
| 32518 | 202007241 | 24/07/2020 | 20200724 | Day | 23/07/2020 20:00 | 24/07/2020 6:00 | 24/07/2020 8:00 | 24/07/2020 18:00 |
| 32519 | 202007242 | 24/07/2020 | 20200724 | Night | 24/07/2020 8:00 | 24/07/2020 18:00 | 24/07/2020 20:00 | 25/07/2020 6:00 |
| 32520 | 202007251 | 25/07/2020 | 20200725 | Day | 24/07/2020 20:00 | 25/07/2020 6:00 | 25/07/2020 8:00 | 25/07/2020 18:00 |
| 32521 | 202007252 | 25/07/2020 | 20200725 | Night | 25/07/2020 8:00 | 25/07/2020 18:00 | 25/07/2020 20:00 | 26/07/2020 6:00 |
| 32522 | 202007261 | 26/07/2020 | 20200726 | Day | 25/07/2020 20:00 | 26/07/2020 6:00 | 26/07/2020 8:00 | 26/07/2020 18:00 |
| 32523 | 202007262 | 26/07/2020 | 20200726 | Night | 26/07/2020 8:00 | 26/07/2020 18:00 | 26/07/2020 20:00 | 27/07/2020 6:00 |
| 32524 | 202007271 | 27/07/2020 | 20200727 | Day | 26/07/2020 20:00 | 27/07/2020 6:00 | 27/07/2020 8:00 | 27/07/2020 18:00 |
| 32525 | 202007272 | 27/07/2020 | 20200727 | Night | 27/07/2020 8:00 | 27/07/2020 18:00 | 27/07/2020 20:00 | 28/07/2020 6:00 |
| 32526 | 202007281 | 28/07/2020 | 20200728 | Day | 27/07/2020 20:00 | 28/07/2020 6:00 | 28/07/2020 8:00 | 28/07/2020 18:00 |
| 32527 | 202007282 | 28/07/2020 | 20200728 | Night | 28/07/2020 8:00 | 28/07/2020 18:00 | 28/07/2020 20:00 | 29/07/2020 6:00 |
| 32528 | 202007291 | 29/07/2020 | 20200729 | Day | 28/07/2020 20:00 | 29/07/2020 6:00 | 29/07/2020 8:00 | 29/07/2020 18:00 |
| 32529 | 202007292 | 29/07/2020 | 20200729 | Night | 29/07/2020 8:00 | 29/07/2020 18:00 | 29/07/2020 20:00 | 30/07/2020 6:00 |
| 32530 | 202007301 | 30/07/2020 | 20200730 | Day | 29/07/2020 20:00 | 30/07/2020 6:00 | 30/07/2020 8:00 | 30/07/2020 18:00 |
| 32531 | 202007302 | 30/07/2020 | 20200730 | Night | 30/07/2020 8:00 | 30/07/2020 18:00 | 30/07/2020 20:00 | 31/07/2020 6:00 |
| 32532 | 202007311 | 31/07/2020 | 20200731 | Day | 30/07/2020 20:00 | 31/07/2020 6:00 | 31/07/2020 8:00 | 31/07/2020 18:00 |
| 32533 | 202007312 | 31/07/2020 | 20200731 | Night | 31/07/2020 8:00 | 31/07/2020 18:00 | 31/07/2020 20:00 | 1/08/2020 6:00 |
| 32534 | 202008011 | 1/08/2020 | 20200801 | Day | 31/07/2020 20:00 | 1/08/2020 6:00 | 1/08/2020 8:00 | 1/08/2020 18:00 |
| 32535 | 202008012 | 1/08/2020 | 20200801 | Night | 1/08/2020 8:00 | 1/08/2020 18:00 | 1/08/2020 20:00 | 2/08/2020 6:00 |
| 32536 | 202008021 | 2/08/2020 | 20200802 | Day | 1/08/2020 20:00 | 2/08/2020 6:00 | 2/08/2020 8:00 | 2/08/2020 18:00 |
| 32537 | 202008022 | 2/08/2020 | 20200802 | Night | 2/08/2020 8:00 | 2/08/2020 18:00 | 2/08/2020 20:00 | 3/08/2020 6:00 |
| 32538 | 202008031 | 3/08/2020 | 20200803 | Day | 2/08/2020 20:00 | 3/08/2020 6:00 | 3/08/2020 8:00 | 3/08/2020 18:00 |
| 32539 | 202008032 | 3/08/2020 | 20200803 | Night | 3/08/2020 8:00 | 3/08/2020 18:00 | 3/08/2020 20:00 | 4/08/2020 6:00 |
