Forum Discussion
Having trouble getting Offset function to work - trying to calculate performance vs prior week
My calendar table looks like this. I inserted custom column to calculate offset weeks from today.
I am now trying to create a measure like this from something I saw on here.
The problem is that it will not let me pull in my actual calendar table (Called "Calendar Week to Date" )
Am I doing this wrong?
3 Replies
It looks like the DAX you're having trouble with is for a calculated column, whereas you're trying to create a measure. While they both use DAX code, you've usually gotta be more careful with measures because of the different contexts in a visual vs a table - i recommend taking a read of these two articles:
https://www.sqlbi.com/articles/row-context-in-dax/ and https://www.sqlbi.com/articles/filter-context-in-dax-explained-visually/
Now to fix your issue - you can try to wrap your 'Calendar'[Date] with MAX, i.e
Week offset = var StartOfWeek = MAX('Calendar'[Date]) - WEEKDAY(MAX('Calendar'[Date]), 2) + 1) ... etc.But if you're trying to calculate the previous week, you should also be able to do the same by using the DATESINPERIOD function: https://learn.microsoft.com/en-us/dax/datesinperiod-function-dax
I would do something like:
Previous Week Measure = CALCULATE([Current Week Measure], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date], -7, DAY))If you need more detailed help, you can try be more specific about what your expected inputs and results are.
