How to add a dynamic date column "Current Week Start Date" to a Fact table

[Deevo_]

Hi all,

Thanks in advance, I have tried to search but I don't know what I need to search for.

What I am trying to achieve:

• I have a fact table which I need to add a dynamic date column which will have the same date value populated for every single row.
• This value changes automatically from week to week depending on the local system date.
• This dynamic date value is the first date of the current week.
  • I.e. The First day of the week is Sunday. This means the first date of the current week = 7th May 2023, then when it hits Sunday the date will change to the 14th May 2023 etc.

Why I need this "Current Week Start Date": 

• I need to determine if a staff member has an active planned working hours for the current week.
• I will use their planned end date of their roster in my calculation.
  • The basic rule is: if the planned end date is GREATER than the "(start date of the current week) minus 1 day", then 'True', otherwise 'False'.

What I currently have:

• Fact table
  • This contains the Staff Planned Hours for the Month
  • This table does not have a datekey i can join to the date dimension table.
• Date Dimension table
  • Not really sure how to use this to bring in the current start date of the week.

Expected results below:

Staff Name	Planned Start Date	Planned End Date	Current Week Start Date(minus 1 day) I.e. (07/05/2023) -1 day	Is this roster active? I.e. Planned End Date > 06/05/2023
Alex	01/05/2023	04/05/2023	06/05/2023	False
Bec	05/05/2023	06/05/2023	06/05/2023	False
Carl	07/05/2023	10/05/2023	06/05/2023	True

[Deevo_ · Accepted solution]

Managed to solve this myself:

1. Added a custom column in my fact table: Current Date = DateTime.LocalNow()
   • Then changed the type to "date".
2. Then added another custom column in my fact table: Current Week (Start Date) = Date.AddDays(Date.StartOfWeek([Current Date]),-1)
   • The minus 1 means to return the date for a Sunday, rather than the default Monday.
   • Then changed the type to "date".
3. Then added a conditional column to work out if the Planned roster dates was active during this week.
   • IF(Planned End Date >= Current Week Start Date, "True", "False)

Thanks