Date Formula Support Needed

Bennets04

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

 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,830
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​
 

Bennets04

Board Regular
Joined
Jul 30, 2010
Messages
58
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
Joined
Jul 30, 2010
Messages
58
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
Joined
Oct 24, 2015
Messages
5,830
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,601
Messages
5,487,795
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top