Hi
Having searched extensively I know this is a question that has come up lots but I just cannot manage to get prior week sales in the format I need. I'm trying to calculate week vs prior week performance but the latest week is determined by a slicer.
The data behind the report is, in its simplest format:
Department Week Sales
Dep1 201910 100
Dep 2 201910 100
Dep 1 201911 150
Dep 2 201911 150
In a matrix table I have departments as rows (Dept 1, Dept 2 etc) and the first column (as values) is a sum of week X sales, with week X being detrmined by a slicer. With the week filter as 201911 (stores as a number but effectively week 11 of 2019) the matrix shows:
Department Sales
Dep 1 150
Dep 2 150
Total 300
I'm trying to add a measure that, when week 201911 is selected in the slicer, pulls through the sales for the prior week i.e. 201910, but this needs to be relative to the week selected in the slicer (so always pulls the week prior to the week in the slicer) so that it looks like this:
Department Sales Prior Week Sales
Dep 1 150 100
Dep 2 150 100
Total 300 200
I can do this manually (approach 1):
Prior Week Sales = calculate(SUM(sales_table[Sales]), sales_table[Week]=202010)
This ignores the filter of 201911 and pulls through the sum of 202010 which is great, but it's static. I need the 202010 element of the formula to be one less than the week in the slicer. I've tried using selectedvalue(Sales_Table[Week])-1 in place of the hardcode 201010 but I can't use selected value inside the calculate function (I get an error message)
I've tried as an alternative :
calculate(SUM(sales_table[Sales]), filter(sales_table, sales_table[week]=(selectedvalue(sales_table[Week])-1))) but that only works when I take the '-1' off the end (i.e. returns sum of 201911, not 201910). I think that's because it's limiting what it can sum based on the filter already applied.
So I then tried (approach 2):
calculate(SUM(sales_table[Sales]), filter(all(sales_table), sales_table[week]=(selectedvalue(sales_table[Week])-1)))
This then produced:
Department Sales Prior Week Sales
Dep 1 150 200
Dep 2 150 200
Total 300 200
So now the table is pulling the correct total from week 201910 but it's putting that total into every department as though it's lost the association with the departments on the left.
In summary, approach 1 pulls the numbers exactly as they should be but it's static and I can't seem to make it variable based on the slicer. Approach 2 is variable based on the slicer but pulls through the total sales for the correct week against every row in the table.
Apologies if I've not been clear - I'm new to DAX but I'm slowly undertsanding more and more but I feel a little out of my depth now.
Notes - I've tried to simplify everything above. In the full dataset there are many departments and sub-departments. The week is numeric and consecutive, and I only have sales by week not by day.
I'm willing to try anything but I don't understand why I have two 'almost there' solutions and I can't find soemthing that works!!
Thank you
