cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryan-gao Member
Member

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
scottsen Senior Member
Senior Member

Re: Another DAX formula close to DATEADD

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

ryan-gao Member
Member

Re: Another DAX formula close to DATEADD

Thanks scotten,please forgive my poor English. 

Highlighted
marcorusso Member
Member

Re: Another DAX formula close to DATEADD

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 Member
Member

Re: Another DAX formula close to DATEADD

QQ截图20160831212228.jpg

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

ryan-gao Member
Member

Re: Another DAX formula close to DATEADD

QQ截图20160831233230.jpg

am I right?

 

v-micsh-msft New Contributor
New Contributor

Re: Another DAX formula close to DATEADD

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

ryan-gao Member
Member

Re: Another DAX formula close to DATEADD

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

v-micsh-msft New Contributor
New Contributor

Re: Another DAX formula close to DATEADD

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

 

ryan-gao Member
Member

Re: Another DAX formula close to DATEADD

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 155 members 1,675 guests
Please welcome our newest community members: