Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
4 years ago
Solved

Can a Lookup Value return Last week values?

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 progr...
Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    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.

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    H_insight
    Icon for Helper V rankHelper V
    4 years ago

    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