Creating a measure to show testing completed last week

[Anonymous]

Hi, 

I am trying to create a measure to show the number of tests that were changed to testing complete last week. I have created a week column that has assigned all change dates a week number e.g. 14/10/2024 change date is week 42.

I have experimented with many different formats and do not seem to be getting anywhere:

CALCULATE(COUNTROWS(' Project Test'[Project Test Completed],

KEEPFILTER(MAX('Project Test'[Week]-1))))

Project Test Completed is a measure I have already created that pulls all project tests in status testing complete. The week measure has generated a column that ascribes a value to all rows based on when the item was changed to complete. I am lost as how to proceed. Please help! 

[Anonymous]

That worked a treat - thank you so much! 

[bhanu_gautam · Accepted solution]

@Anonymous , Try using 

LastWeekTestsCompleted =
VAR CurrentWeek = MAX('Project Test'[Week])
VAR LastWeek = CurrentWeek - 1
RETURN
CALCULATE(
[Project Test Completed],
'Project Test'[Week] = LastWeek
)