Get data till last friday(previous week) dynamically based on date column

[kkanukurthi]

Hi All,

I need help on creating a custom column in power query. I have a Date column with past and future dates. say from 2024 to 2025. check below screen shot.

Need a custom column so that column should have dates calculated till Friday. Lets say today is 25th July 2024. I need to get data till last Friday 19th July 2024 including past data. Future dates(from 20th july) and data should be ignored/not considered. whenever it is autorefreshed the data should be taken till 19th july/last friday. check below screen shot.

Likewise, if the week changes 29th july Monday to 3rd August Saturday , the data should be picked up till last/previous week friday date i.e 26th July including past data.

It should repeat every week, the data and Date should be picked up till last/previous week friday whenever it refreshes(dynamically)  either it is refreshed on following Monday or any day in week.

Please help.Thanks in advance

[Anonymous · Accepted solution]

Hi @kkanukurthi ,

Sorry for being late! Back to work now!
You can change the custom column into this:

if [Date] <= Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday) - #duration(3, 0, 0, 0) then 1 else 0

Here is the whole M function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZJBDsMgDAT/knOkxgaD/Zao//9GW9W7RuE6AjNr9r6P+ZKXXtqP8xh6vM8fURJL0kDalaSDSEtiIDgyABRjJojjkoNEggCwmUQuoOlAdDYMEkorEa2NF0u7A9mmKTQPSlBdEE/orjy12yvtA+OV9o5ZSvs2gGjfYa+0J7H93thS69w2qL79n8Y2q1HeiSgf/y/zKlBIkkqToLLgEqO0noRRLAGDzJGEOVST+ONbfCkQ/ZiiORCdG15bCgTybL0v/TGeovfkqfHYpC/9oZU/PsWX+nSMqvoYl0t3JaK74MGqT8C06hN4sfozsIfqD5dc/REi2jsCVX8Ms6o/Aa/qz/yeen8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] <= Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday) - #duration(3, 0, 0, 0) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ronrsnfld · Accepted solution]

You can use the Table.SelectRows method to return only those rows earlier than or equal to last Fridays date.

Here is code that generates a sample table and shows you the filter.

Paste this code into the Advanced Editor in a blank query to see how it works.

let

//sample table
    Source = Table.FromColumns(
        {List.Dates(#date(2024,1,1),365,#duration(1,0,0,0))}
        & {List.Numbers(1,365)},
        type table[Date=date, Data=number]),

    #"Up to Last Friday"=Table.SelectRows(Source, each [Date] <= Date.StartOfWeek(Date.From(DateTime.FixedLocalNow()),Day.Friday))
    
in
    #"Up to Last Friday"

[Anonymous]

I would do this by first adding a query that gives you the date for the most recent Friday, and then use this query as a parameter in your main query's date filter. First query named FridayDate (in a new blank query):

= let Today = Date.From(DateTime.LocalNow()) in 

if Date.DayOfWeek(Today) = 7 then Date.AddDays(Today, -8) else if Date.DayOfWeek(Today)= 6 then Date.AddDays(Today, -7) else if Date.DayOfWeek(Today)= 1 then Date.AddDays(Today, -9) else Date.AddDays(Today, (8+ Date.DayOfWeek(Today))*-1)

This gives you last Friday's date, or the Friday before that if today is Friday, Saturday, or Sunday.

Then you can use this query as a parameter in you date filter, so in your main query, add:

= Table.SelectRows(PriorStepOrTableName, each [Date] <= FridayDate)

--Nate

[Anonymous]

Hi @kkanukurthi ,

Maybe you can try this way:
Here is my sample data (from 2024.7.1 to 2024.8.31), and Today is 2024.7.26 Friday:

Use this M code to create a custom column:

let
    Today = Date.From(DateTime.LocalNow()),
    CurrentDayOfWeek = Date.DayOfWeek(Today, Day.Friday),
    MostRecentFriday = if CurrentDayOfWeek = 0 then Today else Date.AddDays(Today, -CurrentDayOfWeek),
    Result = if [Date] <= MostRecentFriday then 1 else 0
in
    Result

And the final output is as below:

Then you can filter the column "Custom" which equals 1:

And the final output is as below:

The final result will change according to the date.

Here is the whole M code in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZJBDsMgDAT/knOkxgaD/Zao//9GW9W7RuE6AjNr9r6P+ZKXXtqP8xh6vM8fURJL0kDalaSDSEtiIDgyABRjJojjkoNEggCwmUQuoOlAdDYMEkorEa2NF0u7A9mmKTQPSlBdEE/orjy12yvtA+OV9o5ZSvs2gGjfYa+0J7H93thS69w2qL79n8Y2q1HeiSgf/y/zKlBIkkqToLLgEqO0noRRLAGDzJGEOVST+ONbfCkQ/ZiiORCdG15bCgTybL0v/TGeovfkqfHYpC/9oZU/PsWX+nSMqvoYl0t3JaK74MGqT8C06hN4sfozsIfqD5dc/REi2jsCVX8Ms6o/Aa/qz/yeen8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
    let
        Today = Date.From(DateTime.LocalNow()),
        CurrentDayOfWeek = Date.DayOfWeek(Today, Day.Friday),
        MostRecentFriday = if CurrentDayOfWeek = 0 then Today else Date.AddDays(Today, -CurrentDayOfWeek),
        Result = if [Date] <= MostRecentFriday then 1 else 0
    in
        Result),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kkanukurthi]

Thankyou for detailed solution. Can we restrict the dates in date column to last week/most recent friday should be 19th July. 

Like, if today's date falls anyday between 22nd july Monday to 28 July Sunday , the dates should be taken till last week friday i.e 19th July. In custom column, the filter '1' should be till 19th July only. Remaining from 20th july should be '0'.

The same way if the week passes, say Im in 29th July monday, The data and Dates should be picked up till 26th Friday. The custom coulmn filter '1' should be till 26th july and remaining from 27th july should be '0'

Note : whether it is Monday to Sunday any day , the data should be picked till previous week friday only.

PLease Help. Thanks

[Anonymous · Accepted solution]

Hi @kkanukurthi ,

Sorry for being late! Back to work now!
You can change the custom column into this:

if [Date] <= Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday) - #duration(3, 0, 0, 0) then 1 else 0

Here is the whole M function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZJBDsMgDAT/knOkxgaD/Zao//9GW9W7RuE6AjNr9r6P+ZKXXtqP8xh6vM8fURJL0kDalaSDSEtiIDgyABRjJojjkoNEggCwmUQuoOlAdDYMEkorEa2NF0u7A9mmKTQPSlBdEE/orjy12yvtA+OV9o5ZSvs2gGjfYa+0J7H93thS69w2qL79n8Y2q1HeiSgf/y/zKlBIkkqToLLgEqO0noRRLAGDzJGEOVST+ONbfCkQ/ZiiORCdG15bCgTybL0v/TGeovfkqfHYpC/9oZU/PsWX+nSMqvoYl0t3JaK74MGqT8C06hN4sfozsIfqD5dc/REi2jsCVX8Ms6o/Aa/qz/yeen8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] <= Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday) - #duration(3, 0, 0, 0) then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1))
in
    #"Filtered Rows"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PwerQueryKees]

Once more, have a look at:

Date.StartOfWeek([Date], Day.Saturday)

This will give you the last Friday before the date given...

[PwerQueryKees]

You are looking for Date.StartOfWeek([Date], Day.Saturday)

More info here: Date.StartOfWeek - PowerQuery M | Microsoft Learn

And you probably want to use: DateTime.LocalNow - PowerQuery M | Microsoft Learn

[kkanukurthi]

Can you please show me with sample data or provide me Mquery please. If it is not possible through power query please provide dax formula.

Thanks