Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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):
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
Solved! Go to Solution.
The problem you are having with your second measure is the ALL(sales_table). It is removing all filters (including store) that come into the calc. What you want is ALL(sales_table[week]).
The calc can be made more clear using a variable. Give this a try:
Prior Week Sales = VAR PriorWeek = SELECTEDVALUE ( Sales_Table[Week] ) -1 RETURN CALCULATE( SUM ( Sales_Table[Sales] ), ALL ( Sales_Table[Week] ), Sales_Table[Week] = PriorWeek )
The problem you are having with your second measure is the ALL(sales_table). It is removing all filters (including store) that come into the calc. What you want is ALL(sales_table[week]).
The calc can be made more clear using a variable. Give this a try:
Prior Week Sales = VAR PriorWeek = SELECTEDVALUE ( Sales_Table[Week] ) -1 RETURN CALCULATE( SUM ( Sales_Table[Sales] ), ALL ( Sales_Table[Week] ), Sales_Table[Week] = PriorWeek )
thanks for your help
That's immense - thank you very much!
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |