davidmg1982
Board Regular
- Joined
- Oct 12, 2015
- Messages
- 64
Im having a lot of trouble working with this formula, what im trying to get is, if the week number is even, then get the difference compared with two weeks ago (current- twoweek), else (current 53-previous 52), the thing is with other measures works perfect, but with this particular measure is giving me another number, any suggestion on how to achive this result the correct way? thanks ahead for your time and advise! The result should be 8.78 - 8.33 = 0.45
CM_EN_NonMBAM_2W_Variance =
VAR TwoWeek =
CALCULATE (
[CM_EN_NonMBAM],
FILTER (
ALL ( 'Date' ),
'Date'[WeekNum]
= MAX ( 'Date'[WeekNum] ) - 2 ))
VAR PreviousWeek =
CALCULATE (
[CM_EN_NonMBAM],
FILTER (
ALL ( 'Date' ),
'Date'[WeekNum]
= MAX ( 'Date'[WeekNum] ) - 1 ))
VAR CurrentWeek =
CALCULATE (
[CM_EN_NonMBAM],
FILTER (
ALL ( 'Date' ),
'Date'[WeekNum]
= MAX ( 'Date'[WeekNum] )))
RETURN
IF(ISEVEN(CurrentWeek),(currentWeek-TwoWeek), (currentWeek-PreviousWeek))
CM_EN_NonMBAM_2W_Variance =
VAR TwoWeek =
CALCULATE (
[CM_EN_NonMBAM],
FILTER (
ALL ( 'Date' ),
'Date'[WeekNum]
= MAX ( 'Date'[WeekNum] ) - 2 ))
VAR PreviousWeek =
CALCULATE (
[CM_EN_NonMBAM],
FILTER (
ALL ( 'Date' ),
'Date'[WeekNum]
= MAX ( 'Date'[WeekNum] ) - 1 ))
VAR CurrentWeek =
CALCULATE (
[CM_EN_NonMBAM],
FILTER (
ALL ( 'Date' ),
'Date'[WeekNum]
= MAX ( 'Date'[WeekNum] )))
RETURN
IF(ISEVEN(CurrentWeek),(currentWeek-TwoWeek), (currentWeek-PreviousWeek))