Date Formula Support Needed

[Bennets04]

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

 

[sandy666]

what does that mean: Last Week -1? Last Week - one day? - one week?

anyway try:

[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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Date[/td][td][/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]NthWeek[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10/07/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

10/07/2019​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/07/2019​

[/td][td][/td][td]

02/07/2019​

[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

25/06/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

25/06/2019​

[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

18/06/2019​

[/td][td][/td][td]

18/06/2019​

[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10/06/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

10/06/2019​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

18/07/2019​

[/td][td][/td][td]

18/07/2019​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

03/07/2019​

[/td][td][/td][td=bgcolor:#E2EFDA]

03/07/2019​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]
[/table]

 

[Bennets04]

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]

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]

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