Last Week function not working

[user35131]

I'm trying to filter and get "last week data" based on latest dataset. 

e.g. if my latest input is on January 16(Week 3) I want data from January 8 to January 14(Week 2)

This function works

Last Week = CALCULATETABLE('Table',FILTER('Table','Table'[WeekNum]= MAXX('Table','Table'[WeekNum])

))

However when I want to add Year as a condition it fails to run.

Last Week = CALCULATETABLE('Table',FILTER('Table',AND('Table'[WeekNum]= MAXX('Table','Table'[WeekNum]),'Table'[Year]= MAXX('Table','Table'[Year]))

))

However when i do this it works. The second function based on the code should Filter 2 and 2022. 

Last Week = CALCULATETABLE('Table',FILTER('Table',AND('Table'[WeekNum]= 2,'Table'[Year]= 2022)

))

[sevenhills]

for quick testing, can you check what you are getting values for 

MAXX('Table','Table'[WeekNum])

MAXX('Table','Table'[Year])

[user35131]

I got "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[sevenhills · Accepted solution]

A) The formula to the measure has to be adjusted.

B) Check this post solution

https://community.powerbi.com/t5/Desktop/DAX-function-to-show-last-weeks-visits/m-p/2140649

C) FYI, CALCULATETABLE returns table. You need to add something to use and get what you want later ... Check this for the sample on how to use SUMX

https://docs.microsoft.com/en-us/dax/calculatetable-function-dax

Additional links to get last week:

https://amitchandak.medium.com/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3 

https://www.daxpatterns.com/week-related-calculations/ 

https://www.vahiddm.com/post/weekly-time-intelligence-dax

Typical Measures, we all do with week

Current Week = 
var _Dt = MAX([Date])
var _Week = WEEKNUM(_Dt)
var _Year = YEAR(_Dt)

RETURN _Year & "-" & _Week

Latest Week Sales 2 = CALCULATE(Sum(SalesFact[Revenue]), FILTER('Date', YEAR('Date'[Date]) & "-" & WEEKNUM('Date'[Date]) = SalesFact[Last Week]))

(or)

Latest Week Sales = 
var _Dt = MAX([Date])
var _Week = WEEKNUM(_Dt)
var _Year = YEAR(_Dt)

RETURN CALCULATE(Sum(SalesFact[Revenue]), FILTER('Date', and(WEEKNUM('Date'[Date]) = _Week, YEAR('Date'[Date]) = _Year)))