Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Noyer
Helper I
Helper I

Previous Week Sales

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

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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
)

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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
)
Anonymous
Not applicable

thanks for your help

That's immense - thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.