Forum Discussion
Post ProdigyWeek in Power Query
Hi Good day,
Can anyone pls need help on my table below, How can i display on my table using power query the previuos week and previuos previuos week.
Let say now is Week 48, I want on my table to be display only is Week 47 and 46.
Week 49, diplay only Week 48 and 47.
Thank you
Hi AllanBerces - you can add a current week column in tables as below:
Date.WeekOfYear(DateTime.LocalNow())
This will give you the current week number.
Add a column to calculate the "Relative Week" by subtracting the WeekN column from the current week number:
WeekN=Date.WeekOfYear([Date])
Relative Week= [Current Week] - [WeekN]
This will create a column where:
0 represents the current week.
-1 represents the previous week.
-2 represents the week before that.
Filter Rows:Apply a filter to the "Relative Week" column to include only rows where the value is -1 or -2.
After applying this query, your table will only display the relevant weeks (e.g., Week 47 and 46 if the current week is 48).
Hope it works .
3 Replies
- rajendraongole1
Super User
Hi AllanBerces - you can add a current week column in tables as below:
Date.WeekOfYear(DateTime.LocalNow())
This will give you the current week number.
Add a column to calculate the "Relative Week" by subtracting the WeekN column from the current week number:
WeekN=Date.WeekOfYear([Date])
Relative Week= [Current Week] - [WeekN]
This will create a column where:
0 represents the current week.
-1 represents the previous week.
-2 represents the week before that.
Filter Rows:Apply a filter to the "Relative Week" column to include only rows where the value is -1 or -2.
After applying this query, your table will only display the relevant weeks (e.g., Week 47 and 46 if the current week is 48).
Hope it works .
AllanBercesHi rajendraongole1 thank you very much for the reply its work great.
- ThxAlot
let Source = let today = DateTime.LocalNow(), offset = Date.DayOfWeek(today, Day.Monday) in List.Transform({1..14}, each Date.From(today-#duration(_+offset,0,0,0))), #"Last 2 Wks" = Table.FromList(Source, Splitter.SplitByNothing(), type table[Date=date]) in #"Last 2 Wks"
