IF statement with date

Question

Hi All,&nbsp;Sorry for the basic question, i am new to PowerBI and slowly learning it!&nbsp;&nbsp;I have a table which has the following columns - Name (File Name), Extension (File type e.g .txt), size (size of the file), lastaccessed.&nbsp;I would like to add a custom column to say that if the lastaccessed is within the last 1 year, call it hot data, otherwise if it's not been accessed in 1-3 years, call it cold, otherwise if it's older than 3 years then it will be archive data.&nbsp;Please could someone advise on the best way to do this?&nbsp;Many thanks in advance.Matt&nbsp;&nbsp;

edhans · Answer

Do this in Power Query as a custom column. Look at the Date functions.&nbsp; The&nbsp;Date.IsInPreviousNYears() function in particular would seem to do exactly what you want.

anonymous · Answer

Hi Edhans. Thanks for your quick response. I've just tried to add the query but it's not quite stacking up. Please see below.

Surely the files that were accessed the day before yesterday as per the query below, should be hot. As you can see in the screenshot there's a number of files that were accessed on 25th March. Maybe i'm not understanding it properly.

edhans · Answer

Try this:

anonymous · Answer

Hi edhans unfortunately that didn't work.It just says cold all the way down the list and for some reason I get loads of duplicate rows.Thank you for your responses.

edhans · Answer

I'd need to see your data. It&nbsp;will work. DateIsInPreviousNDays() simply returns true or false. Your original formula was comparing a date to the result DateIsInPreviousDays() but since the latter returns true/false, it will never equal any date, so the If statement always returned the FALSE result.&nbsp;&nbsp;

Forum Discussion

IF statement with date

8 Replies

Related content

Date IF Statement

OR Statement ?

Converting Epoch Date fields within the import statement

IF statement for dates

SQL Statement opcion error