Calendar Table for a busines that operates two shifts in a day

[Anonymous]

Hi Gurus,

I am going to start off by creating my date table and would like to know that if my company operates two production shifts per day (6pm to 6am next and second shift is from 6am to 6pm same day), is it advisable to do my dates column in the date table also start from 6pm and ending 6 pm the next day OR doing a standard date column from 12 am midnight to 12 pm noon will better suit.

Most of the analysis will be done on shifts like last two shifts average production or last 14 shifts average for example.  What type if date column (6am to 6am or 12am to 12am) is best?

[Anonymous]

Thanks, I already have a date table from 01/07/21 to 30/06/22, how can I create shift rank in that table instead of creating an additional table through the query you provided above?

Also where the following column will be created ? In fact table or in date table?

Date Shift no = Format([Date], "YYYYMMDD")  & [Shift No]

sorry for asking too many questions.

Thanks in advance.

[Anonymous]

@amitchandak That's awesome mate. I have been reading your replies and impressed with the way you help the community. Thank you for that.

And yes, my date table is linked with shift end date so that when I select let's say 8th of December from the calendar table I get both the shifts filtered as well (shift that ends on 6am on 8th Dec and shift that ends on 6pm on 8th Dec as well)

Just one more question, in one of my matrix visual I filter the data by last operated shift and I get the required results because most if the measures work with just one shift selected.

However, when on the same matrix I want to have average of last two shifts for yesterday I don't get it with just last shift selected on filters. However when I select full day that is 8th of December then I get the average of two shifts but then my other numbers get wrong. This is spinning my head. 

How can I have the two shifts measures added to the matrix where most of the measures are set on the basis of just last shift selection.

[amitchandak · Accepted solution]

@Anonymous , Your data table should join either shift start or sift end date.

But you can have shift table with at and shift Number or start time

Date , Shift No

Now you can have rank on this combination

Date Shift no = Format([Date], "YYYYMMDD")  & [Shift No]

Shift Rank = rankx(Shift, [Dare Shift No],,asc, dense)

This Shift = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Shift'),'Shift'[Shift Rank]=max('Date'[Shift Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Shift'),'Date'[Shift Rank]=max('Date'[Shift Rank])-1))

Last 8 Shift = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Shift'),'Date'[Shift Rank]>=max('Date'[Shift Rank])-8 && 'Date'[Shift Rank]<=max('Date'[Shift Rank])))

[Anonymous]

Thanks @amitchandak , how do I add shift No column in the table? Can you please help by giving me the dax for it

Thanks,

[amitchandak]

@Anonymous ,

In main table

shift no column = If(hour([Datetime]) >=6 && hour([Datetime]) <18 , 1,2)

Date table with shift

DateShift Table = crossjoin(Calendar(date(2018,01,01), date(2021,12,31)) , union(row("Shift",1) ,row("Shift",2)))