topic Re: Show quantity of last calendar week based on todays date always in DAX Commands and Tips

Show quantity of last calendar week based on todays date always

magnifybi — Sat, 06 Jun 2020 11:59:01 GMT

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

Re: Show quantity of last calendar week based on todays date always

lbendlin — Sat, 06 Jun 2020 12:06:18 GMT

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

Re: Show quantity of last calendar week based on todays date always

magnifybi — Sat, 06 Jun 2020 12:08:05 GMT

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

Re: Show quantity of last calendar week based on todays date always

lbendlin — Sat, 06 Jun 2020 12:13:55 GMT

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.

Re: Show quantity of last calendar week based on todays date always

magnifybi — Sat, 06 Jun 2020 12:38:14 GMT

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?