Calculate value from previous week

[Anonymous]

Hi guys,

Could someone please tell me what is wrong with my expression?

CaseFill_LastWeek =

Calculate([CaseFill], filter(all(table),table[Year]=MAX(table[Year]) && table[Week#]=WEEKNUM(max(table[Date].[Date])-5)))

Cheers

[v-xicai · Accepted solution]

Hi @Anonymous ,

If you need to calculate value from previous 1 week, you can change the measure like DAX below.

CaseFill_LastWeek =CALCULATE([CaseFill], FILTER(ALLSELECTED(table),table[Year]=MAX(table[Year]) && table[Week#]=MAX(table[Week#])-1))

Or you can create calendar table first of all, create relationship between your data table and calendar table on date field, then create measures like DAX below.

Calendar table= CALENDARAUTO()
 
CaseFill_LastWeek = CALCULATE([CaseFill],DATEADD('Calendar table'[Date],-7,DAY))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Grambi]

Try to change the weeknumber part to

table[Week#]+1=WEEKNUM(MAX(table[Date]))