Date Formula Support Needed

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
65
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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 :LOL:

DateDateNthWeek
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​
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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...

see also: Power Query M Reference
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top