Question re Date Diff (Weeks from Today) - Not Filtering Correctly On Sunday

[Peppearson]

Hi, 

I have added a column as part of my calendar date table that calculates calendar "Weeks from today", please see DAX below :

Weeks From Today 2 = VAR Offset = WEEKDAY('Calendar_Table'[Date],2)

RETURN INT((Today() - ('Calendar_Table'[Date] - Offset) ) /7)

Note - Use of offset to achieve Mon to Sunday week

I use this to filter my reports for certain calendar weeks i.e current or previous week.

Sunday Problem

During the week the field works correctly so for example at the date of creating this post 12th August, when the field is filtered to weeks from today = 0 (i.e current week). It will return all dates between Monday 8th August and 14th August.

However on Sunday this filter returns dates from the next week (not current calendar week). 

My report is currently only refreshed on Sunday's so my workaround is to set all relevant weeks from today filters to 1 instead of 0 so that they return the correct dates.

Any ideas what might be causing this issue on a Sunday?

[v-chenwuz-msft · Accepted solution]

Hi @Peppearson ,

Is that you want something like this gif shows?

Please refer the pbix file in the end.

Best Regards

Community Support Team _ chenwu zhu

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

[amitchandak]

@Peppearson , move both to Sunday or Monday and then try

Weeks From Today 2 =

VAR _date= WEEKDAY('Calendar_Table'[Date],2)

VAR _today= WEEKDAY(Today,2)
RETURN

quotient(datediff(_date,_today,day),7)

[Peppearson]

Thank you for your response.

Just to clarify:

VAR _date= WEEKDAY('Calendar_Table'[Date],2) Returns the day number i.e. if the calendar date is a monday it would return 1.

VAR _today= WEEKDAY(Today,2) Returns the day number  - Same as above but returns day number for today() function so for today it'd return 5

RETURN

quotient(datediff(_date,_today,day),7) - Returns the remainder of division of the result of date diff/7 

As far as I can tell this would not return the "weeks from today" for the calendar dates? 

[v-chenwuz-msft · Accepted solution]

Hi @Peppearson ,

Is that you want something like this gif shows?

Please refer the pbix file in the end.

Best Regards

Community Support Team _ chenwu zhu

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

[Peppearson · Accepted solution]

Hi, thanks for the reply.

This isn't quite what I had in mind - my solution needs to show the date diff from the current date from any data in the past/future.

I've actually received a working solution from stack overflow. The title of the post is Date Diff Calculating Incorrectly When Refreshed On Sunday - https://stackoverflow.com/questions/73335573/date-diff-calculating-incorrectly-when-refreshed-on-sun....