topic Re: Having trouble getting Offset function to work - trying to calculate performance vs prior week in DAX Commands and Tips

Having trouble getting Offset function to work - trying to calculate performance vs prior week

DeerCamp — Thu, 27 Jun 2024 20:39:03 GMT

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?

Re: Having trouble getting Offset function to work - trying to calculate performance vs prior week

vicky_ — Thu, 27 Jun 2024 22:49:20 GMT

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.

Re: Having trouble getting Offset function to work - trying to calculate performance vs prior week

DeerCamp — Fri, 28 Jun 2024 14:30:59 GMT

Thank you for your help!

Any idea why the measure I copied from you is not working?

Re: Having trouble getting Offset function to work - trying to calculate performance vs prior week

DeerCamp — Sun, 30 Jun 2024 15:36:18 GMT

Help?