Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating previous week numbers based on slicer values.

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))

 

 

 

Need Help

1 Reply