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
gauravnarchal
Post Prodigy
Post Prodigy

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

 

gauravnarchal_0-1597593591195.png

 

3 ACCEPTED SOLUTIONS
Sujit_Thakur
Solution Sage
Solution Sage

Dear @gauravnarchal .
This measure will do the work 
Yed .JPG

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 

View solution in original post

v-eqin-msft
Community Support
Community Support

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:

8.17.2.png

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

View solution in original post

@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 

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

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:

8.17.2.png

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
Super User
Super User

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

TomMartens_2-1597594710410.png

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:

TomMartens_3-1597594874525.png

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
pranit828
Community Champion
Community Champion

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

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Sujit_Thakur
Solution Sage
Solution Sage

Dear @gauravnarchal .
This measure will do the work 
Yed .JPG

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 

@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 

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.