Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Filter

HI Guys,

 

I have a quick question:
- Lets say I filtered my entire report on Calendar Date is in "Previous Week"

- But I need one of my measures to check if something was assorted in the last 6 months

 

I used the formula:

Assortment = CALCULATE([Assorted], all('Calendar'), DATESBETWEEN('Calendar'[Calendar Date], today() -180, today()))
 
Is the measure actually calculating on the last 6 months or is the report level filter only limitting it to previous week data ? If the latter, then how do I proceed
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

    The page filter only changes the displayed visual, the calculating result still take the last 6 months value.

    For example, I have a measure calculate the last month value, when I set page filter to before 2020/2/1. The result 193 doesn't change, only the visual is filtered. 

     

    Best, regards

    Paul Zheng

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    I would expect that the ALL should get rid of the page level filter if it is filtering the Calendar table.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Seems fine or you can try like example below

    Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-6,MONTH))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

    The page filter only changes the displayed visual, the calculating result still take the last 6 months value.

    For example, I have a measure calculate the last month value, when I set page filter to before 2020/2/1. The result 193 doesn't change, only the visual is filtered. 

     

    Best, regards

    Paul Zheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Paul, 

       

      That's what I thought thanks a lot for confirming ! 😄 

       

      The weird thing is that for another measure of the same type, I was trying to sum all shipments that were made in the last three weeks. Knowing that weeknum([Last Saturday]) is 8 and that the visual is filtered on week 8 data only (Variable Fiscal Week is set to Previous Week), I assumed that the below formula would give me the result the sum of shipments made on week 6, 7 and 8. Unfortunately, it only sums the shipments made on week 8, thus limited by the report level filter. 😞 

       

      Transit = CALCULATE(SUM(Shipments[Gross Units]),

      ALL('Calendar'[Variable Fiscal Week]), 

      filter(all('Calendar'),'Calendar'[Calendar Week] > (weeknum([LastSaturday],1) - 3) && 'Calendar'[Fiscal Week Num] <= (WEEKNUM([LastSaturday],1)))

       

      Data:  I have

      Calendar week = 6   -> Shipments = 6 units

      Calendar week = 7   -> Shipments = 3 units

      Calendar week = 8   -> Shipments = 12 units

       

      But Transit measure results in 12 (shipments on week 8, Weeknum of last saturday)

      Instead of summing of the three and be 21 units

       

      Any idea on why it is doing this and how to resolve ? would greatly appreciate