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)
- YearNo
YEAR('Calendar[Date])
tks & krgds, -nik