Defusing a Time Bomb - PowerPivot/SQL new year change

[mrshl9898]

Hi all,

I'm building a report that needs to include housebills(consignments) and revenue last week, last week last year, last 4 weeks and last 4 weeks last year. These need to be Mon-Fri, Sat and Sun aren't a big concern which week they fall into.

Have managed a solution. But it's nowhere close to perfect.

Could only manage to pull in 465 days of data into PowerPivot to make sure I had it all. And group it by week.

set transaction isolation level read uncommitted;

Select
CustomerCode,
CustomerName,
WeekNumber,
YearNumber,
COUNT(Housebillnumber) as Cons,
SUM(TotalInvoiced) as Revenue
from
(
    SELECT
    CustomerCode,
    CustomerName,
    DATEPART(wk, shipmentdate) AS WeekNumber,
    YEAR(shipmentdate) as YearNumber,
    Housebillnumber,
    TotalInvoiced
    FROM mytable
    WHERE ShipmentDate >= DATEADD(DAY, -465, GETDATE())
) AS sst

GROUP BY
CustomerCode,
CustomerName,
WeekNumber,
YearNumber

Then added the following into PowerPivot to get the results:

RevenueLastWeek =IF(Data[YearWeek]=Data[ThisWeekNumber]-1,Data[Revenue],0)
RevenueLast4Weeks =IF(AND(Data[YearWeek]>=Data[ThisWeekNumber]-4,Data[YearWeek]<=Data[ThisWeekNumber]-1),Data[Revenue],0)
RevenueLastWeekLastYear =IF(Data[YearWeek]=Data[ThisWeekNumber]-101,Data[Revenue],0)
RevenueLast4WeeksLastYear =IF(AND(Data[YearWeek]>=Data[ThisWeekNumber]-104,Data[YearWeek]<=Data[ThisWeekNumber]-101),Data[Revenue],0)

The biggest challenge here is in week 202101 this will be useless for a few weeks.

Can anyone suggest a better method?

Many thanks as always,
mrshl