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
Matty21202
Regular Visitor

IF statement with date

Hi All,

 

Sorry for the basic question, i am new to PowerBI and slowly learning it! 

 

I have a table which has the following columns - Name (File Name), Extension (File type e.g .txt), size (size of the file), lastaccessed.

 

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.

 

Please could someone advise on the best way to do this?

 

Many thanks in advance.

Matt

 

 

8 REPLIES 8
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

tableexample.png

Try this:

image.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

I'd need to see your data. It 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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

HI edhans, this is my data.

 

I'd like an additional column called 'hot' and then 'cold' and then 'Archive'. I think i'm struggling with the logic with PowerQuery. I've ordered a book that's arriving today so i can further learn it. I normally deal with PowerShell so dates are slightly different in that language.

 

This is so i can then add a visual to say Hot would be a certain price for data, COld would be another price, then Archive would be another price. Maybe this is easier with 3x columns (Hot, Cold, Archive)?

Thanks again.


@edhans wrote:

I'd need to see your data. It 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.

 

 


 

tableexample.png

Managed to get the Hot or Cold working, however it's creating duplicate rows for some reason?

 

tableexample.png

The formula isn't creating extra rows. You have a merge that is causing that. I just tested it with random data and it works just fine. This just looks for dates in the previous 10 days. If so, it is Hot, otherwise Cold. I didn't bother changing future dates to show something different.

 

Please post a link to your model or a more complete set of data than screenshots to help with the merge issue.

 

image.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.