Dax | week-on-week calc including the year crossover

DAX
1

hi,

kindly ref previous posting blw

Measure previous week - DAX / DAX Calculations - Enterprise DNA Forum

may i also know how to write dax formula to calculate week-on-week (w-o-w) & week-on-week growth percentage (w-o-w %) that also handles the first and last weeks of each year, pls?

tks & krgds, -nik

2

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)

  1. YearNo
    YEAR('Calendar[Date])

tks & krgds, -nik