Add offset columns for time intelligence

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!

Date.xlsx

Date Time

Transformation

Accepted answers

jrlindem

@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.

jrlindem_example.yxzp

All comments

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

DateOffsets.yxmd

jrlindem

@jsamstad

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.

jrlindem_example.yxzp

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. 😉

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703