Forum Discussion

Peppearson's avatar
Peppearson
Helper I
4 years ago
Solved

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

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?
 
 
 
 
 
 
 
 
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Peppearson
      Helper I
      4 years ago

      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?