Set Analysis help for WeekEnd expression -1week, -2week & -3Week

[#1]

HI

I have a pivot table which displays open Maintenance calls. I want 3 expressions across the top that show the quantity of open calls for the weekending for this week, last weeks week end and the week before lasts weekend.

I'm having no problem showing the total for a fiscal year:

=sum({1<[Calendar Fiscal Year]= {'2015-2016'},[Maintcon CallStatus]={'Open'}>}[Maintcon Count])

I tried using the WeekEnd() function like this and this shows the total that were opened in that week end period not the total that was still open/outstanding at that date:

=sum({1<[CalendarWeekEnd]= {'$(vWeekEnd)'}, [Maintcon CallStatus]={'Open'} > }[Maintcon Count])

          The variable  $(vWeekEnd)  is set to :=WeekEnd(Today())

I thought I could just stick a -7 after the Today()-7 but this does not work.

Thanks

Nick

[sunny_talwar · Accepted solution]

May be you need this:

This Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today()), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today()), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])

Previous Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today(), -1), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today(), -1), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])

Previous 2 Week

=Sum({1<DateField = {"$(='>=' & Date(WeekStart(Today(), -2), 'DateFieldFormatHere') & '<=' & Date(WeekEnd(Today(), -2), 'DateFieldFormatHere'))"}, [Maintcon CallStatus]={'Open'}, CalendarWeekEnd> }[Maintcon Count])

[#3]

Perfect