The Single equation would be.
if [Parameter].[Week] = datepart('week',[Date] then [Sales]
The YTW Equation
if [Parameter].[Week] >= 1 and [Parameter].[Week] <= datepart('week',[Date] then [Sales]
You may have to adapt that depending on how you want to deal with years.
1 of 1 people found this helpful
Here is my approach
1.create parameter for Week
2. Create a calculated filed for Selected week sales.
3. Create second calculated field for YTD calculation
Note: I have taken Current year because if I will not do the same, I have 5 years data and all weeks will be there in rest 5 years data. In this case it will aggregate all the numbers for all the year. If you want to aggregate then you can remove year check from both calculations.
Thanks alex, that is what I was looking for. One last question i have to show occupancy% for that given week as well.
I have occupancy%= SUM([Num Sales])/[bedsum].
I have used weeklyOccupancy = if [Parameters].[Week] = datepart('week',[Week End Date]) then [Occupancy %] END
BUt getting a error cant mix aggregate and non aggregate comparisons or results if expression
Not sure what would be causing the non-aggregate vs aggregate error, you have to make sure that your results are evenly yoked, i.e. both resultants are aggregates or non-aggregates. This article may help with that.
Thanks for the response sandip, how do we show sales for the previous week, that is say when parameter is showing 22 then the sales should be showing sales for week 21. is it possible.
Great, Thank you for your share.