I am attempting to calculate previous week numbers for order count based on a slicer value input.
I am using a date table and have created a relationship between it and my data table based off a date value in the data table. The slicer selection is using the date table to pass the current selected value to my measure. What I have attempted is below.
LW Total Order Count =
var PREVWEEK=SELECTEDVALUE('Ship Calendar'[WeekNum])
RETURN
CALCULATE(DISTINCTCOUNT('Data'[Order_Id]),'Data'[Reason_Code]="",'Data'[Order_Id]<>"",'Data'[Most Recent] ="yes",FILTER(ALL('Data'),'Data'[Ship Week]=PREVWEEK))
The issue appears to be when using the ALL() with the other filters withing calculate. It returns a distinct count of all orders for the previous week without applying any filters.Is there a way to maintain previous filters within calculate while also not applying the Ship week filter from the slicer to the calcuation so I don't receive (BLANK) due to both the slicer value and the measure filter being applied simultaneous?
Any help would be greatly appreciated!
EDIT: I was able to solve this utilizing the following measure changes.
LW Total Order Count =
var PREVWEEK=SELECTEDVALUE('Ship Calendar'[WeekNum])-1
var CurrYear=SELECTEDVALUE('Ship Calendar'[Year])
RETURN
CALCULATE(DISTINCTCOUNT('Data'[Order_Id]),'Data'[Reason_Code]="",'Data'[Order_Id]<>"",'Data'[Most Recent] ="yes",FILTER(ALL('Ship Calendar'),'Ship Calendar'[Ship Week]=PREVWEEK),FILTER(ALL('Ship Calendar'),'Ship Calendar'[Year] = CurrYear))
