Measure If Date Less than Another Date

[gauravnarchal]

I need help to create a measure by referring to the below table.

Measure:- If the created_at is less than or equal to 90 days (of Calendar Date) it is "TRUE" else "FALSE".

[Sujit_Thakur · Accepted solution]

Dear @gauravnarchal .
This measure will do the work 

Date_Measure = IF(

SUMX(Sheet1,Sheet1[Created at ])<=(NOW()-90),

"True",

"False")

Please give kudos by clicking thumbs up button , and if it solved then accept this post as solution
please reply if any doubt 

Regards ,

Sujit 

[Anonymous · Accepted solution]

Hi @gauravnarchal ,

According to my understanding, you want to create a True/False flag when the date is within/not within 90 days(Comparing with the last day in the Created_at column), right?

You could use the following formula:

_diff =
IF (
    DATEDIFF (
        SELECTEDVALUE ( 'diff'[Created_at] ),
        CALCULATE ( MAX ( 'diff'[Created_at] ), ALL ( diff ) ),
        DAY
    ) <= 90,
    TRUE (),
    FALSE ()
)

My visualization looks like this:

Is the result what you want? If not, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Anonymous · Accepted solution]

Hi @gauravnarchal ,

According to my understanding, you want to create a True/False flag when the date is within/not within 90 days(Comparing with the last day in the Created_at column), right?

You could use the following formula:

_diff =
IF (
    DATEDIFF (
        SELECTEDVALUE ( 'diff'[Created_at] ),
        CALCULATE ( MAX ( 'diff'[Created_at] ), ALL ( diff ) ),
        DAY
    ) <= 90,
    TRUE (),
    FALSE ()
)

My visualization looks like this:

Is the result what you want? If not, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[TomMartens]

Hey @gauravnarchal ,

what exactly do you mean by "Calendar Date", do mean toda? Do you want to create a calculated column or a measure?

Do you have a dedicated Calendar table, how does this table relate to the table in your picture?

Nevertheless, based on my sample data

This DAX statement

Column = 
var _today = TODAY()
var dateofthecurrentrow = [Date]
var _datediff = DATEDIFF(dateofthecurrentrow , _today , DAY)
return
IF(_datediff < 90 , "True" , "False")

Creates this calculated column:

Regards,

Tom

[Anonymous]

Hi @gauravnarchal 

I would use the below measure.

_90_OrLess = IF(TODAY()-Table[Picking Date]<=90,True,False) //It will result in a boolean data type, if you need as text use IF(TODAY()-Table[Picking Date]<=90,"True","False") 

[Sujit_Thakur · Accepted solution]

Dear @gauravnarchal .
This measure will do the work 

Date_Measure = IF(

SUMX(Sheet1,Sheet1[Created at ])<=(NOW()-90),

"True",

"False")

Please give kudos by clicking thumbs up button , and if it solved then accept this post as solution
please reply if any doubt 

Regards ,

Sujit 

[Sujit_Thakur · Accepted solution]

@gauravnarchal  ,
Please let me know that did you got your answer which i mentioned earlist of all .
which was

Date_Measure = IF(

SUMX(Sheet1,Sheet1[Created at ])<=(NOW()-90),

"True",

"False")

Kindly give kudos to motivate solution authors and please do accept my post as solution if it gave you what you wanted 

Regards ,
Thakur sujit