Forum Discussion

ryan-gao's avatar
ryan-gao
Icon for Helper III rankHelper III
10 years ago

Another DAX formula close to DATEADD

In Definitive guide to dax ,Page 274,there is a formula close to dateadd()

My question is how can this  equivalent formula received the active filter context? there is no max() or other aggregation functions, if active filter context is 8/30,the formula should return 7/30 ,it means all the rows of date[date] in filter should be false,except 7/30, how to understand it? 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    FILTER (
        ALL ( Date[Date] ),
        CONTAINS (
            VALUES ( Date[Date] ),
            Date[Date], DATE ( YEAR ( Date[Date] ), MONTH ( Date[Date] ) - 1, DAY ( Date[Date] ) )
        )
    )

     

    I'm not super sure I totally understand your question, but I think I am mostly explain how this formula works.  The call to FILTER( ) is going to create an iterator/rowcontext walking ALL Date[Date].   Each row will be evaluated for "hey, 1 month ago, was this date in the current filter context?".   That call to VALUES(Date[Date]) is going to bring back the set of all dates... from the original filter context.

     

    tagmarcorusso

    • marcorusso's avatar
      marcorusso
      Icon for Most Valuable Professional rankMost Valuable Professional
      10 years ago

      The real formula is much more complex, because DATEADD only work with contiguous selection of dates and if min/max correspond to first/day of a month, the entire month is returned. So if you have february selected (28 or 29 days), you get 31 days of January as a result. Your example also doesn't work for January, because you should go to december of previous year.

      Take a look at http://www.daxpatterns.com/time-patterns/ if you need a different implementation of Time Intelligence and/or if you want to customize it.

       

      Marco

      • ryan-gao's avatar
        ryan-gao
        Icon for Helper III rankHelper III
        10 years ago

        It seems in january the calculate column can  be transformed  automatically?

      • v-micsh-msft's avatar
        v-micsh-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        Hi ryan-gao,

        No I don’t think so.

        This is not the matter of +1 or -1 here.

        DATEADD function would only show the results based on the current available date, which means the computing date should be within the date range of the date column calculated.

        The close DAX formula:

        filter(all(Datetable[Date]),

                   contains(values(Datetable[Date]),

                      Datetable[Date], date(year(Datetable[Date]), month(Datetable[Date])-1,day(Datetable[Date])

                                                          )

                                )

                      )

        This formula aimed to keep the date function to only calculate the wanted date, which is trying to make the dateadd function understandable, but as it states, the formula is not correct.

        The wrong logic here is the syntax under Contains function.

        CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…) 

        Parameters

        table Any DAX expression that returns a table of data.

        columnName The name of an existing column, using standard DAX syntax. It cannot be an expression.

        value Any DAX expression that returns a single scalar value, that is to be sought in columnName. The expression is to be evaluated exactly once and before it is passed to the argument list.

        See the result under Power BI desktop:

        Regarding the sum function that you write, I don’t think it is available with the visuals, or saying that the formula is not executable. This is the limitation for the sum function:

        SUM(<column>)

        The column that contains the numbers to sum. Date (or saying the date format) are not numbers.

        If any further questions ,please feel free to post back.

        Regards

    • ryan-gao's avatar
      ryan-gao
      Icon for Helper III rankHelper III
      10 years ago

      Thanks scotten,please forgive my poor English.