Forum Discussion
elkjoperik
Helper II
Helper IIIF filter value does not exist, then use other values
Hi! I have a problem I have not found any solutions to. I've made a dynamic this week vs last week metric; Sales PW = VAR CurrentWeek = SELECTEDVALUE(Date[Week Nr]) VAR CurrentYear = SELE...
Hi elkjoperik,
There is a solution. Please refer to the file in the attachment. You need to rebuild the model. It's hard to filter the proper data in the model in which all the data is stored in one table.
Solution = VAR lastweekTotal = CALCULATE ( SUM ( LW_DUMMY[Revenue FY] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[WeekNum] = MIN ( 'Calendar'[WeekNum] ) - 1 ) ) RETURN IF ( ISBLANK ( lastweekTotal ), CALCULATE ( SUM ( LW_DUMMY[Revenue FY] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[WeekNum] = MIN ( 'Calendar'[WeekNum] ) - 1 ), ALL ( Segments ) ), lastweekTotal )Best Regards,
Dale
v-jiascu-msft
Microsoft Employee
Microsoft EmployeeHi elkjoperik,
There is a solution. Please refer to the file in the attachment. You need to rebuild the model. It's hard to filter the proper data in the model in which all the data is stored in one table.
Solution =
VAR lastweekTotal =
CALCULATE (
SUM ( LW_DUMMY[Revenue FY] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[WeekNum]
= MIN ( 'Calendar'[WeekNum] ) - 1
)
)
RETURN
IF (
ISBLANK ( lastweekTotal ),
CALCULATE (
SUM ( LW_DUMMY[Revenue FY] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[WeekNum]
= MIN ( 'Calendar'[WeekNum] ) - 1
),
ALL ( Segments )
),
lastweekTotal
)
Best Regards,
Dale
elkjoperikHelper II
Helper IIWOW! That worked epic!
Just one thing, I also have a YEAR filter, how would I integrate that into this formula ? Because now the formula gets the right week, but it takes sales from all years and not selected year