Custom columns in power query - Adding text to the weekofyear value

[sadavies2906]

I have created a custom column in a power query that returns the week number of a list of start dates using the following

=Date.WeekOfYear([Start Date])

Below is the output I get in the column header "WEEK", I want to get an output that looks like the column header "EXPECTED".

Not only do I need to add the text "Week" in front of the result but I also need the output to use the current day and work out if the start date is in the past with regards the week number and change those to the text "BACKLOG"

So if I opened the file today on the 20/02 which is week 8, all start dates prior to this week would return "BACKLOG", any start dates in this week would return "Week 8" and anything in the future would have its correct week number.

Hope I've explained this ok

Start Date	Week	Expected
01-Apr	14	Week 14
02-Mar	10	Week 10
30-Jan	5	BACKLOG
23-Feb	8	Week 8
22-Feb	8	Week 8
17-Feb	8	Week 8
14-Feb	7	BACKLOG
20-Feb	8	Week 8
10-Jan	2	BACKLOG
15-Jan	3	BACKLOG
02-Feb	5	BACKLOG

 

[SpillerBD]

M Code for your added column.
=if (Date.From (DateTimeZone.LocalNow()) < [Start Date]) then "Week " & Text.From(Date.WeekOfYear ([Start Date])) else "BACKLOG"

 

[sandy666]

I'd suggest this one
if Date.WeekOfYear([Start Date]) < Date.WeekOfYear(DateTime.LocalNow()) then "Backlog" else "Week "&Text.From(Date.WeekOfYear([Start Date]))

 

[sandy666]

actual date for this table is 21/02/2020
week: 8

Start Date	Week of Year	Custom
01/04/2020	14	Week 14
02/03/2020	10	Week 10
30/01/2020	5	Backlog
23/02/2020	8	Week 8
22/02/2020	8	Week 8
17/02/2020	8	Week 8
14/02/2020	7	Backlog
20/02/2020	8	Week 8
10/01/2020	2	Backlog
15/01/2020	3	Backlog
02/02/2020	5	Backlog

 

[sadavies2906]

Thanks both, works exactly as expected, however I forgot to say I will have dates from last year in the "start date" column so these need to flag as "BACKLOG".

Currently because I only asked about week number in initial question a 2019 week 49 is being returned as "Week 49" as its not less than "Week 8", is there a way to say any "Start Date" less than today is "BACKLOG" except for those in the current week?

 

[sandy666]

maybe any representative example?

 

[sandy666]

or try
if Date.WeekOfYear([Start Date]) < Date.WeekOfYear(DateTime.LocalNow()) or Date.Year([Start Date]) < Date.Year(DateTime.LocalNow()) then "Backlog" else "Week "&Text.From(Date.WeekOfYear([Start Date]))

 

[sadavies2906]

Thank you sandy666 works perfectly

 

[sandy666]

I'm glad you are happy
Thanks for the feedback

 

[sadavies2906]

I have been trying to manipulate the code supplied by sandy666 as things have moved on since it was supplied.

if Date.WeekOfYear([Start Date]) < Date.WeekOfYear(DateTime.LocalNow()) or Date.Year([Start Date]) < Date.Year(DateTime.LocalNow()) then "Backlog" else "Week "&Text.From(Date.WeekOfYear([Start Date]))

I am now trying to get the following

Everything prior to and this week to return this weeks number and everything else to return its actual week number. Dates in 2021 can keep the system generated week number, dates in the past must be this weeks number