Finance | Project Operations, Human Resources, ...

Filtering dates before Monday

(0) Share

Report

Posted on by MM-07050053-0

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?

I have the same question (0)

All responses (8)

Answers (0)

Martin Dráb 236,968 Most Valuable Professional on at

Like (0)

Report

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?

Was this reply helpful? Yes No
GirishS 27,825 Moderator on at

Like (0)

Report

Filtering dates before Monday

As Martin mentioned please explain the scenario with examples, so that we can give suggestions.

Thanks,

Girish S.

Was this reply helpful? Yes No
MM-07050053-0 10 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Mohit Rampal 12,563 Moderator on at

Like (0)

Report

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.

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/xpp-date-run-time-functions

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,968 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
MM-07050053-0 10 on at

Like (0)

Report

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()))

Was this reply helpful? Yes No
MM-07050053-0 10 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Martin Dráb 236,968 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No