Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

understanding how filter is applied

Can someone explain how these two measures are different i.e the logic or the principal behind what makes them different

 

sales last year = CALCULATE([Total Sales], FILTER(Dates, DATEADD(Dates[Date], -1, YEAR)))
 
sales last year = CALCULATE([Total Sales], DATEADD(Dates[Date],-1, YEAR))
 
just so that you know how total sales is obtained -> Total Sales = SUM( Sales[Total Revenue] )
 
from my understanding in both cases the date aspect of the measure filters the data.
Need Help
Show and Tell

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    The filters part of a CALCULATE() expression (the part after your measure) accepts filters in the form of tables.  In your two measures, the table you are creating is different.  In the first one, you are filtering the entire Dates table, while in the second you are just using the Dates[Date] column from the Dates table (single column table used as a filter).

     

    If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

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

    Anonymous , The objective of dateadd with -1 year is moving the entire date set a year back. so We should get training year data. This should give data 1 year behind. 

    sales last year = CALCULATE([Total Sales], DATEADD(Dates[Date],-1, YEAR))

     

    Calculate need first parameter as a measure. So if you do not give a measure, you need to have a column with aggregation

     

    See if these can help

    https://www.youtube.com/watch?v=ewjRItLlgG8

    https://www.sqlbi.com/articles/filter-arguments-in-calculate/

    https://databear.com/dax-calculate-the-king-of-all/

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago
      Anonymous If I understood correctly, your question is related to following highlighted section
       
      sales last year = CALCULATE([Total Sales], FILTER(Dates, DATEADD(Dates[Date], -1, YEAR)))
       
      sales last year = CALCULATE([Total Sales], DATEADD(Dates[Date],-1, YEAR))
       
      First you need to understand what DATEADD  function is doing, it is returning a table with single column with dates values, so it shifts the dateset to previous year based on current date context, if you are looking year 2018, it will return all dates of year 2017, if you are looking at 2019, it will return all dates of 2019.
       
      Now the question is why the first measure is not working. Again , you need to know FILTER  is a function, a special note, one should avoid using FILTER expression. it is an iterate function and can have performance implications. 
       
      FILTER function required two arguments, first argument is a table and 2nd argument is a boolean expression, like [Amount] > 1000 or Country = "Canada"
       
      In first measure, you are saying FILTER(Dates, DATEADD(Dates[Date], -1, YEAR), in this case 1st argument is OK, you are passing a Dates table but 2nd argument is wrong, it is not a boolean expression but it is a table with one column of date and that's why it is not working.
       
      Hope it helps.
       

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!