Last week sales issue

[Xavius]

Hi all, 

the measure below

Sales last week = CALCULATE( SUM( Table[sales] ), REMOVEFILTERS( table ), table[week] = MAX( table[week] ) - 1 )  

will work but if put Region column it will be repeted   
for example the total will be 10000. 
if put region columns all regions will be equal 10000

 which is wrong 

 Anyone can help me with this ?  
much appreciated. 

[Anonymous]

Hi @Xavius ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[Anonymous]

Hi @Xavius ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Anonymous]

Hi @Xavius ,

Thank you for reaching out to the Microsoft fabric community forum.

Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by @danextian  Also I want to thank you @Royel , @Ashish_Mathur , @FBergamaschi  for your helpful response. Please feel free to contact us if you have any further questions.

Thank you.

[danextian · Accepted solution]

hi @Xavius 

REMOVEFILTERS removes filters from a column or table. So, when you remove filters from a table, it's expected that the measure returns the same value for all rows - regardless of what columns are added to the visual - except for the column directly referenced in the measure. It’s best practice to use a dedicated dates/calendar table so that filter modifiers apply only to that table and don’t unintentionally affect the entire fact table.

Revenue LW = 
CALCULATE (
    [Total Revenue],
    FILTER ( ALL ( Dates ), Dates[Week Num] = MAX ( Dates[Week Num] ) - 1 )
)

Please refer to the attached pbix.

[Royel]

Hi @Xavius The issue occurs because REMOVEFILTERS( table ) removes ALL filters from the table, including the Region filter, causing every region to show the total sales for the entire dataset.

You have to be more careful about removing the filters. Instead of removing all filters from the table, you should only remove the filter from the week/date column while keeping other dimensional filters like Region.

Here is an example:

Sales last week = 
CALCULATE( 
    SUM( Table[sales] ), 
    REMOVEFILTERS( Table[week] ),  -- Only remove week filter
    Table[week] = MAX( Table[week] ) - 1 
)

Is it helped? ✔ Give a Kudo • Mark as Solution – help others too!

[Xavius]

Hi Royel,  

but I want to be able to see all the weeks for example 

 if I put weeks in slicer and choose one week to see the last week sales of it. And wow%. It will show empty 

 I just want to see last week sales, by week, by region in the same time. 
which is difficult for me now  

thanks 

[Ashish_Mathur]

Hi,

Ensure that there is a Calendar table with a week number column.  Once that is in place, share the download link of the PBI file.

[FBergamaschi]

This is likely to happen if the table in which the column Region is included has no relationship with the table Table

Can you please show the data model?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI