Show values of measure names from previous week and make difference?

Question

My sheet has measure names divided by ops managers and managers filtered to show values only for current week. What I need is to make new column for each of the measures to show values for previous week and then make %diff. I have seen solutions by putting week in row but is it possible this way somehow?

What I tried:

• LOOKUP(sum([missed hours]),-1) (shows values from previous row)
• { FIXED DATEPART('week',[metric_date]),[ops_login_name],[supervisor_login_name]:sum([missed hours])} does nothing, same values as normal
• I even tried separate calc field DATEPART('week',[metric_date])-1 that shows previous week number and then { FIXED [previous_week],[ops_login_name],[supervisor_login_name]:sum([missed hours])}

Answer 1

You can do this by filtering for the desired week in the Measure Value and not in the filter shelf. Create two boolean expressions that isolate your week.

Is this week? : datetrunc('week',{max([metric_date])}) = datetrunc('week',([metric_date]))

Is last week? : dateadd('week',-1,datetrunc('week',{max([metric_date])})) = datetrunc('week',([metric_date]))

Then create week specific measure values with sum(if [Is this week?] then [total_miss_cnt] end)

Repeat for previous week and then you can do a % diff. Make sure to remove and date filtering from the filter shelf.