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
AndreT
Helper I
Helper I

Query Editor - Custom column with specific date

Hi,

I'm trying to build a custom columns where:

 

FIrst Column give result True/False to following: Date has to be in a week which started 4 weeks ago.

2nd column - True/Fals for any dates which are 4 weeks older or more. 

 

Many thanks in advance.

2 REPLIES 2
v-yulgu-msft
Employee
Employee

Hi @AndreT,

 

Please post sample data and illustrate your desired result with examples/image so that I can test for you.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Youliana,

I managed to bulid following queries:

 

Fora  week which happened exactly 4 weeks ago: 

" =if[Date]>=DateTime.Date(Date.AddDays(Date.StartOfWeek(DateTime.FixedLocalNow(),1),-28)) and [Date]<DateTime.Date(Date.AddDays(Date.StartOfWeek(DateTime.FixedLocalNow(),1),-21)) then "TRUE" else "FALSE" "

 

For dates oldet than 4 weeks ago:

" =if[Date]<=DateTime.Date(Date.AddDays(Date.StartOfWeek(DateTime.FixedLocalNow(),1),-28)) then "TRUE" else "FALSE" "

 

It works but dates are moving within a week.

I would like to make it stacic for a week.

 

Example: This week is W/c 19/03. Today is 20/03 but I would still like to have a True outcome for dates falling in W/c12/02

 

At the moment the first query would show me today, dates from 13/02 to 19/02 Incl.  But I would like to keep it 12/02-18/02 Untill end of this week. So, True would only move to 19/02 once we are in W/c 26/03. Does that make sense? 

 

 

 

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.