Show quantity of last calendar week based on todays date always

[magnifybi]

Hi,

I have been trying to get this to work for ages but can't seem to incorporate today's date in my week calculation.

Essentially I want it to look at todays date dynamically, check what week number this day is in and bring me back the quantity for the week before that.

This is what I have;

Quantity Last Week =

CALCULATE([Quantity Total],

FILTER(ALL('vw_DateDim'),

vw_DateDim[WeekOfYear]=(vw_DateDim[WeekOfYear])-1))

Thanks for the help guys,

Jon

[lbendlin]

What should happen when today's date is Jan 1st?

[magnifybi]

Didn't even think of that, but would love it to brink back the last week of the previous year!

[lbendlin · Accepted solution]

Basically you should use variables.  Meta code here:

Var thisweek = weeknumber(today())

Var amount = calculate(xxx,weeknumber[date]=thisweek-1)

Return amount

This will only work for week 2 and up in a year.  If you want a better solution covering multiple years you need to introduce a YearWeek column in your dates table.

Final Caveat: TODAY() uses UTC in the service - this can lead to unexpected results depending where you live.

[magnifybi]

Thanks so much, I think i have it, it seems to not respond to my date slicers which is good, until there is no data.

For instance, If I change the date slicers to full year 2020, or first 6 months it works, but if I change it to July for instance, the measure goes blank, any idea why? Is it because I'm filtering out the data for it to calculate?

Quantity Last Week =

Var thisweek = WEEKNUM(today())

Var amount = calculate(Transactions[Quantity Total],vw_DateDim[WeekOfYear]=thisweek-1)

Return amount

Any way to ignore the date slicers?