Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Filtering dates before Monday

(0) ShareShare
ReportReport
Posted on by 10
I am trying to filter a date column for dates before Monday.  I do not want to use relative days as I want to filter dates prior to Monday and get the same result if I run the filter on a Monday or a Friday.  Is there a way to filter dates this way?
  • Martin Dráb Profile Picture
    Martin Dráb 230,836 Most Valuable Professional on at
    Filtering dates before Monday
    The error message is right: (lessthandate(1-dayOfWk(Today())) is indeed an invalid expression. You can use use those special SysQueryRangeUtil methods there, such as lessThanDate(), but you can't use any X++, as you tried.
     
    Let me repeat my suggestion: I'm not aware of any function meeting your particular requirements, so ask your development team to make it for you.
  • MM-07050053-0 Profile Picture
    MM-07050053-0 10 on at
    Filtering dates before Monday
    I am always referring to the Monday of the current week.  So if it is currently Monday, I would want to filter for anything before today.  If it is Tuesday, I would want to filter for anything before yesterday...and so on.  D365 does not appear to accept the DayOfWK function in the column filters.
     
    Is anyone aware of a similar function or can you provide a syntax that will work for filtering a column?
  • MM-07050053-0 Profile Picture
    MM-07050053-0 10 on at
    Filtering dates before Monday
    That doesn't seem to work.  I keep getting an syntax error or unexpected results.  Heis is a screenshot of dates I am trying to filter:
    Today is Monday, so the formula I want to use is (lessthandate(0)).  That works except it keeps blank values.
     
    However, I want this fomula to know that I want to filter before Monday of this week.  When I try to put in the day of week, I get an error: (lessthandate(1-dayOfWk(Today()))
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,836 Most Valuable Professional on at
    Filtering dates before Monday
    Dear User, can you please explicitly confirm that you want end-users being able to add such filter, or doing it from code works for you too? It sounds like it's the former case, but unfortunately you still didn't say it explicitly. Mohit's suggestion applies to the former case only.
     
    If you want it through GUI, you'll a query range function that you use instead of a fixed value. If you look at Advanced filtering and query syntax, you'll see that there are functions like lessThanDate(). I'm not aware of any function meeting your particular requirements, so ask your development team to make it for you.
  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,552 Super User 2024 Season 1 on at
    Filtering dates before Monday
    Hi, You can use dayOfWk(date) function to find day from today's date and subtract that number from today's date accordingly to get the calculated date and add range on calculated date in executeQuery method of form dataSource. 
     
  • MM-07050053-0 Profile Picture
    MM-07050053-0 10 on at
    Filtering dates before Monday
    My immediate problem is filtering Scheduled Start Dates for work orders in Asset management, but my question applies to any module with a date field.  From the grid view in Active Work Orders, I want to filter for all dates before Monday.  So today is 7/20/23 and Monday's date was 7/17/23.  I want to make a saved filter that will always filter for dates before Monday.  So this week it would be filtering for all dates before 7/17.  When I come in next week, the same filter would filter for all dates before 7/24/23.  I want the filter to calculate Monday's date so I don't have to change the filter every week.
  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    Filtering dates before Monday
    As Martin mentioned please explain the scenario with examples, so that we can give suggestions.
     
    Thanks,
    Girish S.
  • Martin Dráb Profile Picture
    Martin Dráb 230,836 Most Valuable Professional on at
    Filtering dates before Monday
    I can't give you an answer because you didn't tell us enough about your problem.
     
    Where do you want to do it? In code? In a query for a form? Or somewhere else?
     
    Which Monday are you interested in? The last one? If it's Monday, do you want dates from today or from Monday last week?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,836 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans