Incorrect total when using Calculated Month Ago

Questions & Answers
1

Hi,

I’m using a variant of Calculated Month Ago to show 2 motnhs ago instead.
This is my query

(
  [Measures].[Issues created],
  [Time].CurrentHierarchyMember.Level.DateMember(
    DateAdd('m', -2, [Time].CurrentHierarchyMember.MiddleDate)
  )
)

It shows each weeks correctly, but when I add the automatic Row total, the total is incorrect, it shows the Total amount of created issues ever, and not the amount of issues created for that period of time.

image
image255×640 12.1 KB

Does someone knows how to fix this?

Thanks,
Marilou

2

@Marilou

Try this approach:

(
  [Measures].[Issues created],
  [Time].CurrentHierarchyMember.Lag(7) --counting starts from 0
)

It will look 7 weeks before the displayed week

Martins / eazyBI

1 Like
3

@martins.vanags
This worked!
But now I have a different issue.
I also need the Change % and Averages.
I tried a few different approaches for the change% and the Total is always the Sum of all the change % instead of being change% of the 2 totals
The same happens for the average where the total is the SUM of every line

image
image455×787 24.4 KB

Issues created 8 Weeks Ago

(
  [Measures].[Issues created],
  [Time].CurrentHierarchyMember.Lag(9) --counting starts from 0
)

Issues Created 8 Weeks Ago Change%


CASE WHEN (NOT IsEmpty([Measures].[Issues Created 8 weeks ago]) OR
    DateCompare([Time].CurrentHierarchyMember.StartDate, Now()) < 0
  ) AND
  ( [Measures].[Issues Created 8 weeks ago],
  [Time].CurrentHierarchyMember.Lag(9) --counting starts from 0
  ) <> 0
THEN
  [Measures].[Issues Created 8 weeks ago] /
  ( [Measures].[Issues Created 8 weeks ago],
    [Time].CurrentHierarchyMember.Lag(9) --counting starts from 0
  ) - 1
END

For Average Created Issues 8 Weeks Ago, I use the precalculated Calculated > Statistical > Average

Thanks,
Marilou

4

@Marilou

Try this code for “Issues created 8 weeks ago change %” calculation

CASE WHEN
[Time].CurrentHierarchyMember.Name = '$total_aggregate'
THEN
SUM(
  VisibleRowsSet(),
    [Measures].[Issues created]
)
/
SUM(
  VisibleRowsSet(),
        ( 
    [Measures].[Issues Created],
    [Time].CurrentHierarchyMember.Lag(9) --counting starts from 0
    )
)
-
1
ELSE
  CASE WHEN 
  (NOT IsEmpty([Measures].[Issues Created 8 weeks ago]) OR
      DateCompare([Time].CurrentHierarchyMember.StartDate, Now()) < 0
    ) AND
    ( [Measures].[Issues Created],
    [Time].CurrentHierarchyMember.Lag(9) --counting starts from 0
    ) <> 0
  THEN
    [Measures].[Issues Created] /
    ( 
    [Measures].[Issues Created],
    [Time].CurrentHierarchyMember.Lag(9) --counting starts from 0
    ) 
    -1
  END
END

Martins

1 Like
5

Hi Martin

The formula works perfectly, thank you!

I have a problem with the total for my Average 8 Weeks Ago. It shows the total number of issues instead of the average.

I used the pre-calculated Average. Here is the Query

CASE WHEN NOT IsEmpty([Measures].[Issues Created 8 weeks ago]) THEN
  CatchException(
    Avg(VisibleRowsSet(), [Measures].[Issues Created 8 weeks ago])
  )
END

image
image487×635 23.4 KB

Thansk again for all your help :slight_smile:

6

@Marilou

Try this formula for the last column in your report:

CASE WHEN
[Time].CurrentHierarchyMember.name = '$total_aggregate'
THEN
Avg(
Filter(
VisibleRowsSet(), 
[Measures].[Issues Created 8 weeks ago]>0
),
[Measures].[Issues Created 8 weeks ago]
)
ELSE
  CASE WHEN 
  NOT IsEmpty([Measures].[Issues Created 8 weeks ago]) 
  THEN
      Avg(VisibleRowsSet(), [Measures].[Issues Created 8 weeks ago])
  END
END

Martins / eazyBI

1 Like