Week over Week Reporting for Monday

Questions & Answers
1

I am trying to create a report that shows the number of jira issues in various statuses. I want a week over week report status as of “Monday”. I have the “first day in the week” defined as Sunday

I have tried to use the “Current Week” and “Previous Week” weekly members of the time dimension, but that does not work, because the “Previous Week” is basically showing status as of the previous “Saturday”, aka the end of the week. So, if someone were to look at the report on Monday, they don’t see any change in issue counts in the past week. I am trying to create calculated members representing Monday of this week and Monday of last week. I have found that I can used the following to calculate data on a Monday. However they all seem to be reporting the same status. Plus, I cannot find a definition in MDX for the definition of “Monday”, “This Monday”, nor “Last Monday”

Aggregate(
{[Time].[Day].DateMember(‘Monday’)}
)

— And I can use

Aggregate(
{[Time].[Day].DateMember(‘This Monday’)}
)

– And I can use

Aggregate(
{[Time].[Day].DateMember(‘Last Monday’)}
)

So, can someone help me to understand the proper “Monday” construct to use? I believe that then I could use the lag function on “7” days to get the proper previous Monday, assuming that it is not lag 8.

Such As…
Aggregate(
{[Time].[Day].DateMember(‘This Monday’).Lag(7)}
)

Also - is there a better way to do this?

Thank you

2

Go to the “Data Source” options, you can specify when the first day of the weeks starts:

image
image.png830×563 19.3 KB

3

Yes, I have the week starting on Sunday.

4

I have gone with “Monday” defined as

Aggregate(
{[Time].[Day].DateMember(‘Monday’)}
)

And “Last Monday” defined as

Aggregate(
{[Time].[Day].DateMember(‘Monday’).Lag(7)}
)

Not sure if there is a better way. Unfortunately, this loses the default date information that is printed at the bottom of the charts when you use the delivered Weekly calculated members of “Current Week” and “Previous Week”.

5

Hi @CupplesJim

As an alternative, if you have the “Week” members selected in the Time dimension, you can create a new calculated measure that will get the number of issues in each status as of each Monday of the week:

([Measures].[Issues history],
[Time.Weekly].[Day].DateMember(
  DateAddDays(
    [Time].CurrentHierarchyMember.StartDate, 1
  )
))

As you have set Sunday as the start of the week in the eazyBI import settings, it is necessary to add one day to each week’s start date to get Monday. The DateAddDays() function takes care of that.

One other thing I would recommend for this to work as expected - add missing date members to the Time dimension date range - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-AddmemberstoTimedimension.

The report then would look similar to the one below:

48
48887×642 80.1 KB

You can read more about the functions used in eazyBI calculated measures and members on the documentation page - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference.

Best,
Roberts // eazyBI support

1 Like
6

Thank you Roberts. I will research this alternative solution as well. Thank you also for the additional references.