View a KPI for a rolling 13 week period

[jgreen95]

I am trying to calculate a KPI value for a rolling 13 week period.

The Count distinct element works fine, 

[Actual End Date-Inspection Logged.MyCalendar1.WeekEnd] is retuning the correct date

I have then set [Current Week-13} as a variable  using a formula to calculate the correct date.

Using this as my formula:

COUNT(DISTINCT{<[Inspection Category]={"Category A"}, [Actual End Date-Inspection Logged.MyCalendar1.WeekEnd] >[Current Week-13]>} INSPECTION_NOTICE_ID )

Any help would be very useful

[sunny_talwar]

May be this

Count(DISTINCT {<[Inspection Category] = {"Category A"}, [Actual End Date-Inspection Logged.MyCalendar1.WeekEnd] = {">$(=[Current Week-13])>} INSPECTION_NOTICE_ID)

[jgreen95]

Doesn't work unfortunately, do you know any other way to do a count of only rows in a table with a certain date?

[JGMDataAnalysis · Accepted solution]

What does the [Current Week-13] variable contain?

Count(
              {<
                  [Inspection Category] = {'Category A'},
                  [Actual End Date-Inspection Logged.MyCalendar1.WeekEnd] = {">$(=[Current Week-13])"}
              >}
                  distinct [INSPECTION_NOTICE_ID]
)