Excluding Week-ends from prediction/Trend calculations

Questions & Answers
1

Hi,
I would like to remove week-ends from my prediction calculation.

This is my custom measure. It displays Open issues at the end of the time period until today, for all after today, it calculates the average differences between the last 7 time period and uses that average as a prediction trend

CASE 
  WHEN
-- for all time periods until now, including current period
DateAfterPeriodEnd(Now(), [Time].CurrentHierarchyMember.PrevMember)
  THEN
-- measure Open issues is displayed
Val([Measures].[Open issues])
  ELSE
-- 
Val([Measures].[Open issues] -
Sum(
[Time].CurrentHierarchy.Level.CurrentDateMember.NextMember:
[Time].CurrentHierarchyMember,
-- average Open issue changes during previous 7 time periods (days, weeks or months. choose a time member)
  (([Time].CurrentHierarchy.Level.CurrentDateMember.Lag(7),
[Measures].[Open issues])-
([Time].CurrentHierarchy.Level.CurrentDateMember,
[Measures].[Open issues]))
/7
))

Since this formula uses LAG, I’m not sure it would be possible to exclude week-ends.
If this is the case, I could replace my prediction trend by a Linear regression based on the last 7 time period, but here too, I would need to exclude week-ends and I don’t know how.

LinRegPoint(
-- Numeric expression output x
DateToTimestamp([Time].CurrentHierarchyMember.StartDate),
-- Set expression - input period
[Time.Weekly].[Week].DateMembersBetween("9 weeks ago", "this week"),
-- Numeric expression input y
[Measures].[Find_Rate],
-- Numeric expression input x
DateToTimestamp([Time].CurrentHierarchyMember.StartDate)
)

If someone can help modifying the one query or the other to remove week-ends from the calculation, it would be greatly appreciated.

Thanks,
Marilou

2

Do I understand you correctly? You would like to pull in the last 5 workdays and for each workday calculate the change in open issues and get the average change / per day.

I would suggest a bit different approach on how you access the last 5 workdays and calculate the change per day:

You can define a set of last 7 time members filtered by weekday name. If you have a set of members, you can use the function Avg directly there:

AVG(
Filter(
    [Time].CurrentHierarchy.Levels("Day").DateMembersBetween("7 days ago", "Today"),
    NOT [Time].CurrentHierarchyMember.Get('Week day name') MATCHES "Saturday|Sunday"
  ),
 -- open issues in each workday
  [Measures].[Open issues]
  - 
  -- subtract open issues in the previous workday
  ([Measures].[Open issues], 
   [Time].CurrentHierarchy.Levels("Day").DateMember(
  DateAddWorkdays([Time].CurrentHierarchyMember.StartDate,-1))
  )
)

Here is how it could be used in the prediction formula:

CASE 
  WHEN
    -- for all time periods until now, including current period
    DateAfterPeriodEnd(Now(), [Time].CurrentHierarchyMember.PrevMember)
  THEN
    -- measure Open issues is displayed
    Val([Measures].[Open issues])
  ELSE
    [Measures].[Open issues] -
     -- calculate workdays for prediciton periods only as well
    (DateDiffWorkdays("Tomorrow", [Time].CurrentHierarchyMember.NextStartDate) *
    -- average Open issue changes during previous workdays  
    AVG(
      Filter(
        [Time].CurrentHierarchy.Levels("Day").DateMembersBetween("7 days ago", "Today"),
        NOT [Time].CurrentHierarchyMember.Get('Week day name') MATCHES "Saturday|Sunday"
      ),
     -- open issues in the previous workday
     ([Measures].[Open issues], 
      [Time].CurrentHierarchy.Levels("Day").DateMember(
        DateAddWorkdays([Time].CurrentHierarchyMember.StartDate,-1))
      )
     - 
      -- open issues in the workday
     [Measures].[Open issues] 
   )
   )
END

Please note. The open issue change either per day or by any other period could be negative as well and you might get a line representing the potential growth of open issues as well.

You can check those two examples from our demo account on a similar setup:
Open issues trend. Here is our suggested prediction report for open issues:
https://eazybi.com/accounts/1000/cubes/Issues/reports/139130-open-issues-trend
Project prediction report, check the measure Rolling prediction, I used similar principles
https://eazybi.com/accounts/1000/cubes/Issues/reports/187086-project-prediction-report

Daina / support@eazybi.com

1 Like