How to exclude weekends from Date Filter

salipour · February 22, 2019, 9:08pm

I have a date timeline using “Time Fiscal 14 days ago and today” as a date filter. How can I only display week days and not weekends?

malikgp · February 25, 2019, 3:16pm

Hi Sallipour,

I believe DateDiffWorkDays should be usable in this case. Rather than use a filter, create a custom calculated measure so that you get the 14 days ago and today, but with only workdays.

https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members/mdx-function-reference/datediffworkdays

Hope this helps.

– Malik Graves-Pryor

malikgp · February 25, 2019, 4:27pm

Hi salipour,

Just wrote this up. This should be what you’re looking for in terms of a custom calculated measure in the time dimension.

It says give me the Days that are between 14 days ago and today, as well as where the DateDiffWorkDays function returns true for the current day being evaluated. So if it happens to be a Saturday or Sunday it will not show up in your report

Aggregate(
Filter(
	[Time].[Day].Members,
	DateBetween(
		[Time].CurrentHierarchyMember.StartDate,
		'14 days ago',
		Now()
	)
	AND
	DateDiffWorkDays(
		[Time].CurrentHierarchyMember.StartDate, 
		[Time].CurrentHierarchyMember.NextStartDate
	) = 1
)
)