Forum Discussion
H_insight
Helper V
Helper VCan 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...
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
Community Champion
Community ChampionOK, 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.
