# Date Formula Support Needed

#### Bennets04

##### Board Regular
Good afternoon all,
I'm looking for some help with a BI Power Query formula (Trying to cut out lots of manual work) that will take a date from a column (So 10/07/2019) and return back whether that date was Last Week, Last Week -1, Last Week -2, etc up to Last Week -4 in a new column to the right of the date

Any help or support would be incredible

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### sandy666

##### Well-known Member
what does that mean: Last Week -1? Last Week - one day? - one week?

anyway try:

Code:
``[SIZE=1]= if Date.IsInPreviousNWeeks(Date.AddDays([Date],0),1) = true then 1 else if Date.IsInPreviousNWeeks(Date.AddDays([Date],7),1) = true then 2 else if Date.IsInPreviousNWeeks(Date.AddDays([Date],14),1) = true then 3 else if Date.IsInPreviousNWeeks(Date.AddDays([Date],21),1) = true then 4 else null[/SIZE]``
it will give you a week number if date is in previous week (1), previous previous week (2), previous previous previous week (3) or previous previous previous previous week (4) else null

 Date Date NthWeek 10/07/2019​ 10/07/2019​ 1​ 02/07/2019​ 02/07/2019​ 2​ 25/06/2019​ 25/06/2019​ 3​ 18/06/2019​ 18/06/2019​ 4​ 10/06/2019​ 10/06/2019​ 18/07/2019​ 18/07/2019​ 03/07/2019​ 03/07/2019​ 2​

#### Bennets04

##### Board Regular
Hi mate,

This is brilliant thank you. All I meant was any dates from the previous week I want to be able to show LW, any dates from the week before that date would be LW-1 (Last week -1) and so on

I have loads of sales data by day but want to show some graphs to compare weekly data and in Excel i use LW, LW-1, LW-2, LW-3, LW-1 instead of lets say Week 36, Week 35, Week 34, Week 33, Week 32

Cheers
Steve

#### Bennets04

##### Board Regular
So managed to change the 'then 1' to 'then "LW" and it worked a treat!!

How would the same priniples work if i was just looking at weekend performance? How could i show if a date was lets say between Friday - Sunday lastweek then return a 1, Friday - Sunday the week before would be 2 etc etc?

Any thoughts?

Cheers
Steve

#### sandy666

##### Well-known Member
there are more function like:

• Date.IsInPreviousDay
• Date.IsInPreviousMonth
• Date.IsInPreviousNDays
• Date.IsInPreviousNMonths
• Date.IsInPreviousNQuarters
• Date.IsInPreviousNWeeks
• Date.IsInPreviousNYears
• Date.IsInPreviousQuarter
• Date.IsInPreviousWeek
• Date.IsInPreviousYear
etc...

1,106,304
Messages
5,510,503
Members
408,792
Latest member
S_s_s

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...