Forum Discussion
Last week sales issue
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.
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.
8 Replies
- FBergamaschi
Super User
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
- 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.
- 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!
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 nowthanks
- danextian
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.
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.
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.
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.
