DAX Time Intelligence Week

[SVM9698]

Hi All,

I have a question related to DAX on how to dynamicaly pass the filter selected value indide a measure. consider the below code

YTD Target v1 =

VAR DW = SWITCH(TRUE(),SELECTEDVALUE('Last Week Table'[Latest week])="Latest week",WEEKNUM(today(), 15)-3,

SELECTEDVALUE('Last Week Table'[Latest week])="37",WEEKNUM(today()-7, 15)-3,

SELECTEDVALUE('Last Week Table'[Latest week])="36",WEEKNUM(today()-14, 15)-3,

SELECTEDVALUE('Last Week Table'[Latest week])="35",WEEKNUM(today()-21, 15)-3,

SELECTEDVALUE('Last Week Table'[Latest week])="34",WEEKNUM(today()-28, 15)-3,

SELECTEDVALUE('Last Week Table'[Latest week])="33",WEEKNUM(today()-35, 15)-3)

Return

IF(

SELECTEDVALUE([Product Group]) = "CC",

sum([Measure]),

if(SELECTEDVALUE([channel]) = "HC",

CALCULATE(

sum('Targets per week'[HC]),

'Targets per week', 'Targets per week'[Week number] = DW),

if(SELECTEDVALUE([channel]) = "FGo",

CALCULATE(

sum('Targets per week'[FGo]),

'Targets per week', 'Targets per week'[Week number] = DW),

CALCULATE(

sum('Targets per week'[HC]) + sum('Targets per week'[FGo]),

'Targets per week', 'Targets per week'[Week number] =DW)

)))

here based on week selected from a slicer respective  DW is calculated and is passed to below return condition. but in DW variable i'm hardcoding the week number from slicer  but there i need to pass dynamically has week keeps on updating. Hope anybody helps and point me in right direction.

Thanks in advance and regards!

[lbendlin]

You need to use a YearWeek value, or better yet a Date value (divided by 7 if you want), otherwise your logic will break at the year cutover.

Best solution is to have a Calendar table with yearweek column.