I am trying to exclude weekends from the Status over Time graph in the first screenshot below. I am trying to do so by creating this Calculated Member
[Time].DateDiffWorkdays(2021-11-01, 2021-12-17)
My thought process is this formula would give me data for working days from November 11th to December 17th. I have not created a Calculated Member before and need some help with the actual formula. Can someone help me with the code to exclude weekends from my report? Or explain further what I am doing wrong (i’m assuming improper syntax…)?
The error I am receiving is
Formula is not valid:
No function matches signature '<Dimension>.DateDiffWorkdays(<Numeric Expression>, <Numeric Expression>)'
I would suggest creating a calculated member that returns the Issues history measure (the one you are using in the report) only on desired days - when dates are between specific periods and they are not weekends (the day is not 6 or 7). Please try the following:
CASE WHEN
[Time].CurrentHierarchyMember.Get('Week day') < 6 -- when day is less than 6
AND DateBetween( -- and current time member start date is between the dates
[Time].CurrentHierarchyMember.StartDate,
"1 nov 2021",
"17 dec 2021"
)
THEN [Measures].[Issues history] -- show issues history
END
Use Hide empty not to show the dates when no value is displayed. Make sure not to use Nonempty in this case.