topic Re: Can a Lookup Value return Last week values? in Desktop

Can a Lookup Value return Last week values?

H_insight — Wed, 09 Mar 2022 12:27:19 GMT

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

Re: Can a Lookup Value return Last week values?

HotChilli — Wed, 09 Mar 2022 13:46:42 GMT

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)

Re: Can a Lookup Value return Last week values?

H_insight — Wed, 09 Mar 2022 14:10:29 GMT

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

Re: Can a Lookup Value return Last week values?

HotChilli — Wed, 09 Mar 2022 16:21:55 GMT

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.

Re: Can a Lookup Value return Last week values?

H_insight — Wed, 09 Mar 2022 19:56:48 GMT

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