enable solution to filter data by anything that is 2 years or older from current date

[rob_mysbxsec]

i'm trying to reproduce a filter of the dataset that i know how to accomplish in mysql using the following where clause statement

      (r.review_completion_date <= DATE_SUB(curdate() , INTERVAL 730 DAY) or r.production_launch_date <= DATE_SUB(curdate() , INTERVAL 730 DAY));  -- i.e. all the hits where date is 2 years or older from current date

Can i do this with a calculated column, e.g. using DAX where if the mysql where clause is true for a given row i return TRUE allowing me to then use standard issue report filters looking for TRUE hits of that calculated column.

      in-desired-date-range = if (r.review_completion_date <= DATE_SUB(curdate() , INTERVAL 730 DAY) or r.production_launch_date <= DATE_SUB(curdate() , INTERVAL 730 DAY)) = TRUE else FALSE

Would also be nice to know how i enable this filtering at the dataset level in m-code statement either built using some ribbon button wizard i'm overlooking to help set this up or manually entered m-code using the advanced editor.

[ryan_mayu]

@rob_mysbxsec 

is this what you want?

Column = if(ISBLANK(Sheet1[review_completion_date]),if(DATEDIFF(Sheet1[production_launch_date],today(),day)>730 && Sheet1[review_completion_date]<=TODAY(),TRUE(),FALSE()),if(DATEDIFF(Sheet1[review_completion_date],today(),DAY)>730 && Sheet1[review_completion_date]<=today() ,TRUE(),FALSE()))

[rob_mysbxsec]

Hi @ryan_mayu ,

Thanks for the response and input on solution.  Yes that looks like it would work as well. 

Please see reply i posted to iXpert_info below on what i came up with in terms of a Measure that computes the two years ago from today date and then the Column calculation that uses it to produce a True/False result i can filter off of. 

It appears to arriving at the same outcome as what you have proposed but with use of different functions.

Any insights on value of using today() function over utcnow() and use of datediff() vs a Date <= a computed date to represent two years prior to current date?

[ryan_mayu · Accepted solution]

@rob_mysbxsec 

maybe you can try this

Measure = EDATE(today(),-24)

Column = if(ISBLANK(Sheet1[review_completion_date]),if(Sheet1[production_launch_date]<[Measure]&& Sheet1[review_completion_date]<=TODAY(),TRUE(),FALSE()),if(Sheet1[review_completion_date]<[Measure]&& Sheet1[review_completion_date]<=today() ,TRUE(),FALSE()))

[rob_mysbxsec]

Thanks, yes EDATE() and UTCTODAY() work in liue of the DATE() and UTCNOW() functions i was using and have the benefit of returning just the date i care about that is 2 years in the past versus functions i was using that return date time and i don't need or want the time portion. 

Related question do you know how to convert a Measure that contains a datetime value to a date value? 

I was able to achieve this using model | <table> | <measure> | properties | formatting | date time formatting | m/d/yyyy option but i'm curious if there is a function that can be used for this instead that i was not able to find in Date and time functions (DAX) - DAX | Microsoft Docs.

In the following Measures I can use EDATE() and UTCTODAY() to get a date[only] type result two_years_ago measure but using it on a card visual it still includes time.  

Measure / two_years_ago = EDATE( UTCTODAY(), -24 )

Likewise with the two_weeks_ago where i have to use DATE() and UTCNOW() which is returning a datetime type.
Measure / two_weeks_ago = DATE( YEAR(UTCNOW()), MONTH(UTCNOW()), DAY(UTCNOW())-14 )

[ryan_mayu]

@rob_mysbxsec 

you can click the dropdown list of Format and select the format type that you want.

[iXpert_info]

Hey @rob_mysbxsec 

Can you please explain it with some example data?

Thanks
Jay Patel
iXpert Analytics

[rob_mysbxsec]

Related question do you know how to convert a Measure that contains a datetime value to a date value? 

I was able to achieve this using model | <table> | <measure> | properties | formatting | date time formatting | m/d/yyyy option but i'm curious if there is a function that can be used for this instead that i was not able to find in Date and time functions (DAX) - DAX | Microsoft Docs.

In the following Measures I can use EDATE() and UTCTODAY() to get a date[only] type result two_years_ago measure but using it on a card visual it still includes time.  

Measure / two_years_ago = EDATE( UTCTODAY(), -24 )

Likewise with the two_weeks_ago where i have to use DATE() and UTCNOW() which is returning a datetime type.
Measure / two_weeks_ago = DATE( YEAR(UTCNOW()), MONTH(UTCNOW()), DAY(UTCNOW())-14 )

[rob_mysbxsec]

I have come up with a solution that works, and would be interested if you see anything that suggests this is not the best way to achieve the desired result.

first i created a measure 

    two_years_ago = DATE( YEAR(UTCNOW())-2, MONTH(UTCNOW()), DAY(UTCNOW()) )

then i created a column     

    is_two_years_old = IF (
        ('mytable'[review_completion_date] <> BLANK() && 'mytable'[review_completion_date] <= [two_years_ago]) ||
        (ISBLANK('mytable'[review_completion_date]) && 'mytable'[production_launch_date] <= [two_years_ago]),
        TRUE, FALSE
    )

Now i can create a simple report page or visual scoped filter setting on that computed column selecting TRUE filter to include all records that match that 2 years or older test.

[rob_mysbxsec]

Hi @iXpert_info thank you for your followup on this, apologies for not including sample data right up front.

The file downloadable from here contains the following sample data set where i think what i'm looking to do is create a true/false calculated column that is true if review_completion_date is > 2 years older than current date OR production_launch_date is > 2 years older than current date in the event that review_completion_date is blank. 

I'm not able to figure out how to make an OR statement using report page dragNdrop filter settings as well as how to make those be based on a calcuated prior date based on current date, see this post.  That is what has me thinking if i create a calculated true/false column using what i understand to be DAX syntax i could then use simple report page dragNdrop filter setting including only calculated column = TRUE rows. 
Hope that helps clarify the objective and provide data to author/test proposed solution to it.  Thanks in advance for your assistance.