Add offset columns for time intelligence

[jsamstad]

Hello!

I’m trying to create Month, Week, Day offset columns for time intelligence purposes (the previous month offset would be -1, same for previous week). I’ve attached our fiscal calendar and the expected outcome. We are currently in FY26 and period 9 (2/2 is the start of the fiscal year) so it makes adjusting the time confusing. 

Thanks in advance!

[jrlindem]

Can you give some examples of what your expected values are for each offset?

Month:  If _______ then ________

Week:  If _______ then ________

Day:  If _______ then ________

Do you only need the exact offset date or are you looking for all of the other attributes in your attached date dimension to be shown?

[jsamstad]

I've edited the workbook to show what the answer should be. 

[Yoshiro_Fujimori]

Hi @jsamstad ,

I hope this is what you mean by "offset".

Workflow

Formula Tool

  [Month offset] = DateTimeDiff( DateTimeTrim([Date],"month"), DateTimeFirstOfMonth(),"month" )

  [Day offset] = DateTimeDiff( [Date], DateTimeToday(), "day" )

  [Day of Week] = ToNumber( DateTimeFormat([Date],"%w") )

  [Week offset] = CEIL( ( [Day offset] - [Day of Week] + 1 ) / 7 )

Sample Output

Date	Month offset	Week offset	Day offset
2025-11-01	1	1	9
2025-10-31	0	1	8
…
2025-10-26	0	1	3
2025-10-25	0	0	2
…
2025-10-19	0	0	-4
2025-10-18	0	-1	-5
…
2025-10-01	0	-3	-22
2025-09-30	-1	-3	-23

[jrlindem · Accepted solution]

@jsamstad 

Okay, I think I've got what you're looking for.  The Offsets will update dynamically, anchored to today's date.  Meaning, as we move further into the calendar, the Offsets will adjust.  I constrained them to be Year(-2,1) and Month(-4,2) and Week(-4,4).

I'm not gonna lie, I low-key hate the spaghetti factory this workflow turned into, but it's late I'm all about brute-force at the moment 😅

Here's the workflow with some of the results below:

I've also attached the workflow.  

[jsamstad]

You are close! The Day + Week Offset work. The temptation to use the date functions creates issues as the beginning/ending of the month don't always fall on those dates given the 4-4-5 calendar. 

[jsamstad]

Great! I envisioned it would look something like this in Power Query or Alteryx and you didn't disappoint.   😉