Can a Lookup Value return Last week values?

[H_insight]

Hi all,

I have a little challenge that I hope you can help me with.

The sample data I am using holds dummy data for employees and their progress on deliveries. In my main table, I have a progress column, and it gets updated every day.

I am looking to do some analysis to compare the progress made on a weekly basis, based on the selected week. Not sure how to do so in a measure. I have looked online and most of last week calculations are made to return values but not text. Would lookupvalue work in my case?

Can anyone please help in delivering this measure or even a calculated column?

The outcome in excel looks like this:

Sample File (includes pbix & excel file).

Thanks

[HotChilli · Accepted solution]

OK, change the relationship between calendar table and main table - should be 1:m with single filter direction i.e. calendar filters main table.

Replace the date in the visual with the date from the calendar table. (that's why you've created it).

Here's a measure to test:

TProgLastWeek = VAR _weekNo = MAX('Calendar'[Week number]) - 1
RETURN
CALCULATE(MIN(Data[Progress]), 
FILTER(ALL(Data), Data[Issue ID] = MAX(Data[Issue ID]) && Data[Category] = MAX(Data[Category])) , 
FILTER(ALL('Calendar'),'Calendar'[Week number] = _weekNo))

I put it together quickly so please test at your side.

Let me know how it goes.

[H_insight]

Thank you very much for helping me @HotChilli. Result achieved!

[HotChilli]

Can you explain the logic please?

--

The data in the provided pbix and excel is not the same as shown in the picture (Peter Washington category fields are different between weeks)

[H_insight]

Hi @HotChilli 

Thanks for looking into my question.

I have updated the sample file to fix the misalignment. Apologies for that.

The logic is:

For each [Issue_ID], I want to return the [Progress] from “Last Week”

Where [Issue_ID] = [Issue_ID]

And [Category] = [Category]

For example (Peter Washington), on the [Date] 07/02/2022 had [Issue_id] 818384 and [Category] "16. Cat", in the new measure I want to get [Progress] "Not Started" where it goes back 1 week (from the selected date value) which is [Date] 01/02/2022, [Issue_id] 818384 and [Category] "16. Cat".

Hope the above helps?

Thanks