calculate week-on-week analysis taking into account year cross over

[hood2media]

hi,
i have flwg sales data

week 52, 2020
21/12/20   87
22/12/20   22
23/12/20   14
24/12/20   17
25/12/20   49
26/12/20   11
27/12/20   10
----------------
total          210

week 53, 2020
28/12/20   24
29/12/20   22
30/12/20   18
31/12/20   21
----------------
total          85

week 1, 2021
1/1/21       53
2/1/21       10
3/1/21       16
----------------
total          79

week 2, 2021
4/1/21       32
5/1/21       21
6/1/21       10
7/1/21       14
8/1/21       54
9/1/21       21
10/1/21     14
----------------
total          166

the week-on-week (wow) & week-on-week growth % (wow%) sales analysis shud b as flws-

week 53, 2020   85-210 = 125 (or -59.5%)
week 1, 2021     79-85   = -6 (or -7.1%)

week 2, 2021     166-79 = 87 (or 110.1%)

may i have a dax formula to calculate the wow & wow% to produce results such as above, pls?

tks & krgds, -nik

[hood2media · Accepted solution]

i hv found the solutions as flws-

//
//CW_Msr = current week measure
//FW_Msr = week-1 measure
//LW_Msr = week-53 measure
//PW_Msr = previous week measure
// 

VAR CW_Msr = 
   CALCULATE( [Sales], 
      FILTER( 
      ALL('Calendar'), 
      'Calendar'[Week Rank] = MAX( 'Calendar'[Week Rank]) ) 
      ) 

 VAR LW_Msr = 
   CALCULATE( [Sales],
      FILTER( 
      ALL( 'Calendar'[Date] ), 
      YEAR( 'Calendar'[Date] ) = YEAR( MAX( 'Calendar'[Date] )) 
      && 
      WEEKNUM( 'Calendar'[Date], 2 ) = 53 ) 
      ) 

VAR FW_Msr = 
   CALCULATE( [Sales], 
      FILTER( 
      ALL( 'Calendar'[Date] ), 
      YEAR( 'Calendar'[Date] ) = YEAR( MAX( 'Calendar'[Date] ) ) 
      && WEEKNUM( 'Calendar'[Date], 2 ) = 1 ) 
      ) 

VAR PW_Msr = 
   VAR CW = 
      MAX('Calendar'[YearWeekNo])
 
   VAR PW = 
      CALCULATE( 
         MAX( 'Calendar'[YearWeekNo] ), 
         'Calendar'[YearWeekNo] < CW, 
         REMOVEFILTERS( 'Calendar' ) ) 

   VAR PWResult = 
      CALCULATE( [Sales], 
         'Calendar'[YearWeekNo] = PW, 
         REMOVEFILTERS( 'Calendar' ) )
 
   RETURN 
   PWResult 

RETURN 
IF( [CW-Rank] = 1 , 
   [FW-Measure] -[PW-Measure], 

IF( [CW-Rank] = 53, 
   [LW-Measure] - [PW-Measure], 

[CW-Measure] - [PW-Measure] ) 
)

note:

in the date table, i also created flwg calculated columns-

 a. YearWeekNo

'Calendar'[YearNo]*100 + 'Calendar'[WeekNbr]

b. WeekNbr

weeknum([Date], 2)

3. YearNo

YEAR('Calendar[Date])

tks & krgds, -nik