Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to create a filterable measure for Last Week Sales

The following measure is used to get the sales for last week (Selected Date -7)

 

Sales LW = CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Sales),Sales[date]=SELECTEDVALUE(Sales[date])-7))
 
We have used the ALL() so it could not able to filter by another column.So how can i change the measure to filterable.
 
That is i have to filter the LW sales by supplier name.
 
 
 
Need Help

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

     

    try changing ALL to ALLEXCEPT like below.

    Measure = 
CALCULATE(
    SUM( Sales[Sales] ),
    FILTER(
        ALLEXCEPT( Salesm, table[Supplier] ),
        Sales[date] = SELECTEDVALUE( Sales[date] ) -7 
    ) 
)
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Mariusz ,

       

      Thank you for the quick reply...

       

      Will it filter through allexcept () If i have multiple filters from different dim table?

       

      Thank you for your time

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

         

        If your first condition is a fact table "Sales", then the second, third ... conditions can be any column within the Sales Table or any dimension table or column that has a valid relationship that propagates the Sales table.

         

        ALLEXCEPT( Sales, table[Supplier] )

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski