Match Week to Historical Result

[Fiorrie]

Hi Everyone

I have two things which I am trying to connect.

I have the following code, which belongs to the chart on the right.

(RangeSum(Above(TOTAL Sum({$<[qtr_flag]={1},qtr=>}revenue),0,RowNo(Total)))+
RangeSum(Above(TOTAL Sum({$<[qtr_flag]={2},qtr=>}revenue),0,RowNo(Total)))+
RangeSum(Above(TOTAL Sum({$<[qtr_flag]={3},qtr=>}revenue),0,RowNo(Total))))
/
Sum(Total<qtr>{$<qtr_flag={1,2,3},qtr=>}revenue)

I was trying to get only the week from the chart data on the right with the following formula:

=MAX({$<qtr_flag={0}>}inv_week)

However I am being unsuccessful, how can I connect them please? I want to show it next to the Actual Rev vs Target KPI. So it would be as follows 59% vs 62% showing we are behind the revenue.

Another important thing, I need to show the KPI with the data on Monday minus one (so my week is from Monday to Sunday). I was trying the following formulas but none of them works, it is still showing me the latest value (not until yesterday and also not the previous week which I would like to also catch).

Curr Week

Sum({$<Date={'>=$(=Date(WeekStart(Max(Date), 0, 0),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Max(Date), 0, 0),'DD/MM/YYYY'))'}>}[revenue])

Previous Week

sum({$<Date={'>=$(=Date(WeekStart(Max(Date),-1),'DD/MM/YYYY'))<=$(=Date(WeekEnd(Max(Date),-1),'DD/MM/YYYY'))'}>}[revenue])

sum({$<inv_date={"=Date(max({1}inv_date)-1,'YYYY-MM-DD')"}>}revenue)

Many Thanks in advance!