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
mail2vjj
Helper III
Helper III

Calculate if value is between dates

Hi,

 

I am trying to use a logical IF in a calculated column or a measure.

 

I have two dates column, one for Buying and one for Selling. I want the logical IF (or any other formula) to return the result based on if the Selling date falls before, on, or after 1 year of the date in the Buying date column.

 

For eg. In the table below, for the first row:

Buying Date: 18 Nov 2015

Selling Date : 04 May 2017

 

So if selling date is after or on 18th Nov 2016, then the result should be "Long Term" else "Short Term".

 

Like in the last row Buying Date is 17th Oct 2016, so if the selling date ( 24th Apr 2017) is NOT after or on 17th Oct 2017, so the result should be "Short Term".

 

BUYING DATE  SELLING DATE       Term

18-11-1504-May-17Long Term
23-12-1510-May-17Long Term
23-12-1504-May-17Long Term
23-12-1514-Jun-17Long Term
23-12-1504-May-17Long Term
23-12-1504-May-17Long Term
04-01-1604-May-17Long Term
04-01-1604-May-17Long Term
13-01-1604-May-17Long Term
09-09-1604-May-17Short Term
09-09-1604-May-17Short Term
17-10-1624-Apr-17Short Term

 

I tried using the DATEADD formula to add 1 year to the buying date,
IF(
DATEADD(Buying Date, 1, Year) <= Selling Date, "Short Term", "Long Term"
)

but this is giving me the same result for every row, either Short Term or Long Term (depending upon the >< sign).

 

I have created a date table and relationships on both Buying Date and Selling Date columns, with Selling Date relationship column "Active" but it is just not working.

 

If some one can figure out something or some other formula or way, then please help me out.

It will be greatly appreciated.

 

Thank you,

 

Vishesh Jain

2 REPLIES 2
Fuzbe
New Member

Hi Vishesh,

 

If you are creating a calculated column, you'll need to use the DateDiff finction:

 

Please test this formula: =DATEDIFF([Buying Date],[Selling Date],YEAR)

 

The complete solution might look like:

=IF(

    DATEDIFF([Buying Date],[Selling Date],YEAR)>0

    ,"Long Term"

    ,"Short Term"

)

 

 

Hi @Fuzbe,

 

Thank you for taking the time to resopond to my question.

 

However, I have already tried DATEDIFF and the problem with it is that it does not consider decimal number and only takes into consideration a whole number.

 

The problem with my question is that, I want to take into consideration every single day and even a difference of a single day will affect if it is short term or long term, which is why I was using DATEADD as it will add one year exactly to the year number.

 

Anyways, thank you for your response, if you can come up with any other solution to this, kindly let me know.

 

Thank you,

 

Vishesh Jain

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.