Forum Discussion

Jpahl2033's avatar
Jpahl2033
Helper I
4 years ago
Solved

Weekly Sales & Previous Week Sales Measure Not Filtering in Table

Hi,

 

I have two measures going for Sales this week & Sales previous week. When i try to a slicer or use a filter for these items they do not change. They are the aggregate values still.  Can someone please help? I want to be able to use a slicer/filter to pull different text values i.e. Client A, Client B. The measures are below:

 

Thanks!

 

Sales This Week = CALCULATE(SUM('Table1'[OrderedProductSales]),FILTER(ALL('Table1'),'Table1'[WeekRank] =MAX('Table1'[WeekRank])))
 
Sales Last Week = CALCULATE(SUM('Table1'[OrderedProductSales]),FILTER(ALL('Table1'),'Table1'[WeekRank] =MAX('Table1'[WeekRank])-1))
 
 
Need Help
Tips and Tricks
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Jpahl2033 try first to replace the ALL('Table1') with ALL('Table1'[WeekRank])
    Also use variables, but in this case that wasn't the issue, so write this: 

    Sales This Week = 
    VAR _current_week = MAX('Table1'[WeekRank])
    RETURN
    CALCULATE(SUM('Table1'[OrderedProductSales]),FILTER(ALL('Table1'[WeekRank]'),'Table1'[WeekRank] =_current_week))
     
    Sales Last Week = 
    VAR _current_week = MAX('Table1'[WeekRank])
    RETURN
    CALCULATE    (SUM('Table1'[OrderedProductSales]),FILTER(ALL(Table1'[WeekRank]),'Table1'[WeekRank] = _current_week  -1 ))


    In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

4 Replies

  • Jpahl2033's avatar
    Jpahl2033
    Helper I
    4 years ago

    I actually figured it out, but switching out a column name. Thank you so much for your help. I will mark it as completed. 

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Jpahl2033 try first to replace the ALL('Table1') with ALL('Table1'[WeekRank])
    Also use variables, but in this case that wasn't the issue, so write this: 

    Sales This Week = 
    VAR _current_week = MAX('Table1'[WeekRank])
    RETURN
    CALCULATE(SUM('Table1'[OrderedProductSales]),FILTER(ALL('Table1'[WeekRank]'),'Table1'[WeekRank] =_current_week))
     
    Sales Last Week = 
    VAR _current_week = MAX('Table1'[WeekRank])
    RETURN
    CALCULATE    (SUM('Table1'[OrderedProductSales]),FILTER(ALL(Table1'[WeekRank]),'Table1'[WeekRank] = _current_week  -1 ))


    In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

    • Jpahl2033's avatar
      Jpahl2033
      Helper I
      4 years ago

      Thank you for this! It is very close. When I plug in the two new measures you created, the Sales Last Week Numbers do not show up in the table, but the Sales This week numbers do. Have any ideas?

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        Jpahl2033 do you use a sort by column in the model for the one you put on the axis?