Forum Discussion

Xavius's avatar
Xavius
Frequent Visitor
1 year ago
Solved

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 ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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.