Filtering relative dates to get the last 2 shifts moving

[AlexPayette]

Hi,

I am trying to filter my Power BI Report to get the last 2 shifts moving. I have a date dimension table, a time dimension table and a shift dimension table.

My shift dimension table is composed of 2 **bleep**s, the first one is the Day Shift (from 6h AM to 5:59 PM) and the Night Shift (from 6h PM to 5:59 AM). The reference for the day shift is 1 and for the night shift is 2. 

I have a fact table showcasing results for each day related to each shift. What I am trying to do, is always showcasing the last 2 shifts completed, for example we are October 18th at 8h30 AM, then I wanna see the last 2 shifts completed wich is the night shift (from October 17th 6h PM to October 18th 5:59 AM) and the day shift (from October 17th 6h AM to 5:59 PM). 

I would also wanna go back in time, so for example filter my data so I can go back in time.

How can I do this ? Do I need to have a Shift Dimension with all my shift and dates instead of a 2 row dimension table only displaying the day vs. night shift ?

[Anonymous]

Hi @AlexPayette 

Can you provide the sample of your date dimension table and time dimension table ? With the information you have provided so far, I cannot provide a solution that meets your requirements .

Best Regards,
Community Support Team _ Ailsa Tao

[amitchandak]

@AlexPayette , if you have a date time column with shift time, you can create column rank use that in filter

a new column=

rankx(table, [shift date time],,asc,dense)  

[AlexPayette]

Hi, 

Actually my shift table doesn't contain all the shifts, it is only composed with 2 rows like that : 

RefShift	Shift Name	Shift Start Time	Shift End Time
1	Day	6:00:00	18:00:00
2	Night	18:00:00	6:00:00

I also have a dimdate which contains all the dates and a dimtime which contains all the times related to each rows in the fact table.