Query Editor - Custom column with specific date

[AndreT]

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.

[v-yulgu-msft]

Hi @AndreT,

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

Regards,

Yuliana Gu

[AndreT]

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?