DAX for adding offset columns - days, weeks, months, quarters and years

DAX DAX Calculations
1

Hello, I’ve started using offset columns in all of my date tables (days, weeks, months, quarters and years). I’ve found offsets to be a great addition to time intelligence measures.

I’ve found several sources online where I can download M-Code for date tables with the offset columns incorporated into the date table. However, I’m curious if anyone knows how to write DAX for offset columns (days, weeks, months, quarters and years).

Often times I take on clients who have a data model in place and they need me to fix up parts of a report that need maintenance. In these cases there’s a date table already in place and I’d prefer not to wipe it and start over with a new date table with the M-Code.

I’d love to be able to take any existing date table and apply some dax to add offset columns, rather than start from scratch with a new date table.

Does anyone have some sample DAX they can share and perhaps a pbix file with the date table that I can have a look at?

Thanks for your help!

@BrianJ any ideas?

Pete

2

Hi @pete.langlois. Use DATEDIFF.
Greg
eDNA Forum - Date table with Offsets in DAX.pbix (37.4 KB)

2 Likes
3

I know your question asked for a DAX solution, personally I prefer to include this in my M code (allows for filtering options)

see attached example for setting up Year, Month, Quarter, and Week offsets from current.

TEMPLATE w Date Table.pbix (196.9 KB)

4

Thanks so much, @Greg Very elegant solution to what I thought would be much more complex.

5

@pete.langlois,

I knew that based on the type of work you do and how much of it relies on time intelligence that you would quickly become a big fan of offsets. Ever since @Melissa introduced me to them, I’ve been amazed at the number of times it reduces what might otherwise be fairly complex DAX to a line or two.

  • Brian
2 Likes
6

That is wonderful and so useful.