Week Filter in Power Query Editor(M Code)

[Krishna_Newuser]

Hello,

My report will be refreshed once a week (Monday) and it will have the data for the previous week.
For instance, if I’m refreshing the data on (9th December,2024) it will have the data from (1st Dec to 7th Dec). To filter this exact date in Power Query Editor I have used the below condition.

= Table.AddColumn(#"Inserted Date", "Duration", each if [TxnDt] >= Date.From(Date.StartOfWeek(DateTime.LocalNow() - #duration(7, 0, 0, 0),Day.Sunday))

and [TxnDt] <= Date.From(Date.StartOfWeek(DateTime.LocalNow()- #duration(1, 0, 0, 0),Day.Saturday))

then "Yes" else "No")

Now, I needed ensure that the report will have to filter the previous week of the last week data(24th Nov to 30th Nov 2024) and just tired to change the M Code as below but its not giving the required filter.

= Table.AddColumn(#"Inserted Date", "Duration", each if [TxnDt] >= Date.From(Date.StartOfWeek(DateTime.LocalNow() - #duration(14, 0, 0, 0),Day.Sunday))

and [TxnDt] <= Date.From(Date.StartOfWeek(DateTime.LocalNow()- #duration(7, 0, 0, 0),Day.Saturday))

then "Yes" else "No")

Can you please help me to pull the data for this.

[danextian]

Hi @Krishna_Newuser 

Use the following

EndDate

 let 
            //replace 8 with your UTC offset
            CurrentDate = Date.From(DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8))),  
            DayOfWeek = Date.DayOfWeek(CurrentDate, Day.Sunday),  // Get the day of the week (0 = Sunday, 1 = Monday, ...)
            DaysToSubtract = if DayOfWeek = 0 then 7 else DayOfWeek,  // Calculate how many days to subtract to get to the previous Sunday
            PreviousSunday = Date.AddDays(CurrentDate, -DaysToSubtract)  // Subtract the necessary days
        in
            PreviousSunday

StartDate

Date.AddDays(EndDate, -6)

They can be in separate quries.

Filter your table like your nomally would but between ranges. Replace the star and end dates with those two queries.

[Krishna_Newuser]

Hi @danextian 
Thank you for the reply, 
I tried it but didnt worked, not sure what wrong i have done. Is there any possible that M code be changed in my query or will it be possible to share any PBI report from your end.

Thanks in advance!!!

[danextian]

The queries are just a simple as these

Name the queries ans EndDate and StartDate. Filter your table using date ranges and change the start and end  dates. These queries will work just like parameters sans manually indicating the value.

[Krishna_Newuser]

Hello,

Its just replacing all the steps from the applied steps, can you help me here.