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
Narenthir
Frequent Visitor

Power Query

Hi,

Below pic from excel, the similar way i want to do in power bi, spceific date before need to reflect as (base) after the dates should be reflect as (After). Help needed thank in advance.

Narenthir_0-1679982756754.png

 

1 ACCEPTED SOLUTION
ChielFaber
Super User
Super User

When you use a specific date you can use a hardcoded date. For this you can create a custom column with the folloewing if statement and the #date function:

if [Date] < #date(2023, 3, 1) then "Base" else "After".
 
The #date function It works like: #date(year, month,day).
 
When you need a dynamic date you can replace the #date function and its content with a dynamic function like Date.AddDays , Date.AddMonths, Date.AddYears when you substract a number of days/months/years from a given date.
 
For example:
 
if [Date] < 
Date.From(Date.AddMonths(DateTime.LocalNow(),-6))
then "Base" else "After".
 
You will need to wrap the Data.AddMonths function in a Date.From because Date.AddMonths returns a datetime instead of a date and comparing a date  with a datetime will result in an error.
 
 

View solution in original post

2 REPLIES 2
ChielFaber
Super User
Super User

When you use a specific date you can use a hardcoded date. For this you can create a custom column with the folloewing if statement and the #date function:

if [Date] < #date(2023, 3, 1) then "Base" else "After".
 
The #date function It works like: #date(year, month,day).
 
When you need a dynamic date you can replace the #date function and its content with a dynamic function like Date.AddDays , Date.AddMonths, Date.AddYears when you substract a number of days/months/years from a given date.
 
For example:
 
if [Date] < 
Date.From(Date.AddMonths(DateTime.LocalNow(),-6))
then "Base" else "After".
 
You will need to wrap the Data.AddMonths function in a Date.From because Date.AddMonths returns a datetime instead of a date and comparing a date  with a datetime will result in an error.
 
 

Thanks @ChielFaber solution works 

 

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.

Top Solution Authors
Top Kudoed Authors