Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Measure If Date Less than Another Date

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".

 

 

Need Help
Show and Tell
  • Sujit_Thakur's avatar
    Sujit_Thakur
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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

  • Sujit_Thakur's avatar
    Sujit_Thakur
    6 years ago

    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 

5 Replies

  • Sujit_Thakur's avatar
    Sujit_Thakur
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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's avatar
      Sujit_Thakur
      Icon for Solution Sage rankSolution Sage
      6 years ago

      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 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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")

     

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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