Hi,
I have a worksheet in my dashboard that has multiple values being displayed in rows with weeks as the column. I have a parameter filter that allows the user to select their date range based on the below two calculated fields. This all works great and I have no problem with them. What's causing the issue is one of the measure values I now need to display on the row (Retention Rate below) is calculated with data from the previous week. This calculation works correctly but because it's going back one week it's causing the first week in the date range to not show for this one measure. If there a way to make my date range behave differently for this one measure or is there a better way to use the value from the previous week than what I have done? Thanks in advance for your help.
Selected Start Date
CASE [Select Date Range]
WHEN "All" THEN DATETRUNC('month',[Full Date])
WHEN "3 months" THEN DATEADD('month',-3,TODAY())
WHEN "6 months" THEN DATEADD('month',-6,TODAY())
WHEN "1 year" THEN DATEADD('month',-12,TODAY())
WHEN "2011" THEN DATETRUNC('month',#January 1, 2011#)
WHEN "1H 2011" THEN DATETRUNC('month',#January 1, 2011#)
WHEN "2H 2011" THEN DATETRUNC('month',⌗July 1, 2011#)
WHEN "2012" THEN DATETRUNC('month',#January 1, 2012#)
WHEN "1H 2012" THEN DATETRUNC('month',#January 1, 2012#)
WHEN "2H 2012" THEN DATETRUNC('month',⌗July 1, 2012#)
END
Show Dates
DATETRUNC('week',[Full Date])>=DATETRUNC('week',[Selected Start Date])
Retention Rate
SUM([Retained Users])/WINDOW_SUM(SUM([Unique Users]),-1,-1)
I kept thinking along the lines of hiding the data I didn't want in the current view of the charts instead of filtering it and remember something similar I had done for a running sum in another chart that needed to keep adding but not display outside the date range. The fix was to create a week filter with the formula below to use in the Show Dates and Show Dates End calculated fields so that they work the same for the comparisons as before but now they don't actually filter out the actual data. This is working great and fixes the problem.
lookup(attr([Week]),0)
