Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ryan-gao
Helper III
Helper III

Another DAX formula close to DATEADD

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

QQ截图20160830223204.jpg

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? 

9 REPLIES 9
Anonymous
Not applicable

FILTER (
    ALL ( Date[Date] ),
    CONTAINS (
        VALUES ( Date[Date] ),
        Date[Date], DATE ( YEAR ( Date[Date] )MONTH ( Date[Date] ) - 1DAY ( 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.

 

tag@marcorusso

QQ截图20160831233230.jpg

am I right?

 

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:

3.PNG1.PNG2.PNG

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

Hi Michael,Thanks for your patience.

I agree with your point about this " equivalent" formula. It is not correct  most of the time.

Even the formula is wrong,what makes me confused is the "month(Datetable[Date])-1" in the contains function here.

let us suppose using "contains" as a calculate field to compute numbers of the last month

IF the original filter context in the pivot table is "Date[Date]=2016/2/1" , the filter() in contains ( the name of calculate field )should return "Date[Date]=2016/1/1", in order to get this result, the "month(Datetable[Date])+1" in contains function should be "2",which makes the contains function return true at the row "2016/2/1" ,and then month(Datetable[Date]) should be 1.it is "2016/1/1",if i use "month(Datetable[Date])-1" the date makes contains function return true is 2016/3/1,

am i right?

 

QQ截图20160901171831.jpg

ryan-gao,

Well, I think there is some misunderstnding regarding month function.

Let's take an example using the formula in your last post:

Contains :=

 calculate(

     Sum([value]),

     Filter(

         All('Date'[date]),

         Contains(

            values('Date'[date]),

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

                  )

            )

       )

Formula here should be aimed to find the previous one month of current date, as you mentioned, if the current date is "2016/2/1", which 'Date'[date] should be replaced by this date, so month('Date'[date])=2, and month('Date'[date])-1=1, then the contains function would lookup the "2016/1/1" in the 'Date'[date] column, if exists, returns true.

By the way,

"

IF the original filter context in the pivot table is "Date[Date]=2016/2/1" , the filter() in contains ( the name of calculate field )should return "Date[Date]=2016/1/1",

" till here I agreed, but the following

"

in order to get this result, the "month(Datetable[Date])+1" in contains function should be "2",which makes the contains function return true at the row "2016/2/1" ,and then month(Datetable[Date]) should be 1.it is "2016/1/1"

"

should be a little problem here. If the year(date)=2016, then month(date) should equal to 2, month(date)+1=3, which is 2 more than the expected result.

Regards

 

Thank you Michael_Shao

I think the problem is the evaluation context of the contains function in this calculate field.

 

Contains(

               values(Date[date])   // receive the original filter context outside. which is "2016/2/1"

               'Date'[date],       // belongs to values(Date[date])

               date(year('Date'[]date)), month('Date'[date])-1, day('Date'[date])  // evaluated in the row context created by filter()

)

 

so if the answer of the filter() is 2016/1/1, the contains() in it should return true at "2016/2/1",and at the same time the date[date] which is the row context of the filter() is 2016/1/1.

i think only "month('Date'[date])+1" will do this

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

QQ截图20160831212228.jpg

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

Thanks scotten,please forgive my poor English. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.