Difference between last 13 weeks of current year and last 13 weeks of previous year same day or week

[prasadpatsa]

@dm-p /@lbendlin/@Greg_Deckler

Any ideas please?

Hi All,

I have a peacular scenario where in I have to find the difference between the sales of last 13 weeks from now and last year's same period(which is 13 weeks from same day last year). In fact I have few scenarios like 52 week, 10 week, 4 week etc.

I am using the following two methods but not able to acheive. Could not attach the report as being sensitive and also file being multimillion in size. Couldn't keep records that satifies the current years and last years 10 weeks of data and size comeup to 2 million. Please advise with any sample calculations.

Option-1: As my report should show the fullweeks of data until last week, the below delta between these two is giving the me cutshort weeks(for example, its taking from today whihc is not a full week)

Volume_13weekRollingAvg =
var NumDays = 91
var RollingSum =
CALCULATE(
SUM(tblfactcombinesales_daily_agg[agg_salesquantity]),
DATESINPERIOD(tblfactcombinesales_daily_agg[transactiondate],LASTDATE(tblfactcombinesales_daily_agg[transactiondate]),-NumDays,DAY)
)
RETURN
RollingSum/NumDays

************************************

Volume_PRIOR13weekRollingAvg =
var NumDays = 91
var RollingSum =
CALCULATE(
SUM(tblfactcombinesales_daily_agg[agg_salesquantity]),
DATESINPERIOD(tblfactcombinesales_daily_agg[transactiondate],LASTDATE(tblfactcombinesales_daily_agg[transactiondate])-364,-NumDays,DAY)
)
RETURN
RollingSum/NumDays

Diff = Volume_13weekRollingAvg - Volume_PRIOR13weekRollingAvg

***********************************************************************************************************************************************************

Option-2: Dynamic way of choosing the weeks, however I am not able to reproduce the same calculation for the last years 13 weeks

Created a Yearweek number from date.

Test_13week_Average_Volume =
var Numweeks = 13
var CurrentYearWeek = SELECTEDVALUE(tblfactcombinesales_daily_agg[YearWeek])

var AvgLastNweekVolume =
AVERAGEX(
ADDCOLUMNS(
TOPN(
Numweeks,
CALCULATETABLE(
VALUES(tblfactcombinesales_daily_agg[YearWeek]),
tblfactcombinesales_daily_agg[YearWeek] < CurrentYearWeek),
tblfactcombinesales_daily_agg[YearWeek],
DESC
),
"tempvolumes",
[volumes]
),
[tempvolumes]
)
return AvgLastNweekVolume

I have transactiondate & weeknum is my table

[prasadpatsa]

any insights please?