topic Re: Dynamic weekend date in Power Query

Dynamic weekend date

fareedice — Thu, 15 Dec 2022 10:00:13 GMT

I have date table with dates from Year 2021 to 2025.

I want to create a dynamic measure, whenevr power BI report is opened by a user, then the date should display as last weekending date.. ( i.e last friday)

Suppose If i open the report today, the date should display as 9th December, If i open tomorrow, the date should display as 16th December.

Could some one help how to acheive this

Re: Dynamic weekend date

RobertPaegelow — Thu, 15 Dec 2022 10:31:33 GMT

Hello! I hope this will help you...

To create a dynamic measure that displays the last Friday of the current week, you can use the following steps:

Create a calculated column in your date table that uses the "WEEKDAY" function to determine the day of the week for each date. For example, you can use the following formula to create a calculated column named "Day of Week":

Day of Week = WEEKDAY([Date], 2)

2. Create a measure that uses the "CALCULATE" function to filter the date table to only include the last Friday of the current week. For example, you can use the following formula to create a measure named "Last Friday":

Last Friday = CALCULATE( MAX([Date]), FILTER( ALL(Date), [Day of Week] = 6 ) )

This measure will return the last Friday in the current context. For example, if you filter the date table to only include dates from the current week, the measure will return the last Friday of that week. If you filter the date table to include dates from a different week, the measure will return the last Friday of that week instead.
You can then use this measure in your Power BI report to display the last Friday of the current week whenever the report is opened by a user.

Re: Dynamic weekend date

fareedice — Thu, 15 Dec 2022 10:47:09 GMT

Hi Robert..
We are almost there

but we are getting as 12/27/2022 instead 12/16/2022

Re: Dynamic weekend date

RobertPaegelow — Thu, 15 Dec 2022 10:55:47 GMT

Try this instead, Last Friday = CALCULATE( MAX([Date]), FILTER( ALL(Date), [Day of Week] = 6 ), [Date Filter] )

Re: Dynamic weekend date

fareedice — Thu, 15 Dec 2022 11:04:06 GMT

Thank you for the Reply...In this formula...Last Friday = CALCULATE( MAX([Date]), FILTER( ALL(Date), [Day of Week] = 6 ), [Date Filter] )

the last part [Date Filter] is showing error... Do I have to derive any column? sorry i did not get..

Re: Dynamic weekend date

RobertPaegelow — Thu, 15 Dec 2022 11:05:35 GMT

Okay, that's weird. Could you send me the error message??

Re: Dynamic weekend date

fareedice — Thu, 15 Dec 2022 11:06:38 GMT