Calculate totals from previous weeks

[#1]

Hi

I have a maintenance call app that shows all open maintenance calls. What is actually open is simple to report on as I simply SUM the count of all open maintenance calls with status of 'Open'.

Where I am having difficulty is that I must calculate what the total was for last week, the week before and the week before that. I thought this would be simple but my calculations fail me as they enumerate significantly from week to week. I know from our legacy report that the total figures do not climb from week to week but are fairly flat.

I expect the numbers to be out slightly due to reportable flags in the system that I have not yet utilised but they do not enumerate like mine do. See example:

I think it is my logic more than the code. this is the expression I am using to capture all calls that must of been open in that week period. I'd obviously like to calculate everything that WAS open on the weekend date but cannot figure out how to do that with only the live data being update daily from the database. Where I use

Date(WeekStart(Today(),-3), 'DD/MM/YY') & '<=' & Date(WeekEnd(Today(),-3), 'DD/MM/YY'))" is the reporting period. I change the -3 to -2 for week 2 and -1 for week period 1 etc..

Here is my full code for the expression for week ending period of -3 weeks ago, as you  can see I use the open and closed dates of the maintenance call along with the status to try and get the correct figure:

=Sum({1<[Maintcon M Opened] = {"$(='>=' & Date(WeekStart(Today(),-365), 'DD/MM/YY') & '<=' & Date(WeekStart(Today(),-3), 'DD/MM/YY'))"}, [Maintcon CallStatus]={'Open','Awaiting Parts'},  CalendarWeekEnd,
[Hierarchy Region Name] = {'South','West','East','North'}>}[MCall Count])
//opened before window and still open
+
Sum({1<[Maintcon M Opened] = {"$(='>=' & Date(WeekStart(Today(),-3), 'DD/MM/YY') & '<=' & Date(WeekEnd(Today(),-3), 'DD/MM/YY'))"}, [Maintcon CallStatus]={'Open','Awaiting Parts'},  CalendarWeekEnd,
[Hierarchy Region Name] = {'South','West','East','North'}>}[MCall Count])
//opened in window and still open
+
Sum({1<[Maintcon M Closed] = {"$(='>=' & Date(WeekStart(Today(),-3), 'DD/MM/YY') & '<=' & Date(WeekEnd(Today(),-3), 'DD/MM/YY'))"},
[Maintcon M Opened] = {"$(='>=' & Date(WeekStart(Today(),-3), 'DD/MM/YY') & '<=' & Date(WeekEnd(Today(),-3), 'DD/MM/YY'))"},  CalendarWeekEnd,
[Hierarchy Region Name] = {'South','West','East','North'}>}[MCall Count])
//opened and closed in window
+
Sum({1<[Maintcon M Closed] = {"$(='>=' & Date(WeekStart(Today(),-3), 'DD/MM/YY') & '<=' & Date(WeekEnd(Today(),-3), 'DD/MM/YY'))"},
[Maintcon M Opened] = {"$(='>=' & Date(WeekStart(Today(),-365), 'DD/MM/YY') & '<=' & Date(WeekStart(Today(),-3), 'DD/MM/YY'))"},  CalendarWeekEnd,
[Hierarchy Region Name] = {'South','West','East','North'}>}[MCall Count])
//opened before window and closed in window

I'm using this in a pivot table btw