Modify Percent Change From "Month Ago to Week Ago"

Questions & Answers
1

Hello,

Can you provide some advice on how I can alter the following measure to calculate percentage change from “week ago” instead of “month ago?”

CASE WHEN (NOT IsEmpty([Measures].[Time to resolution - Average hours]) OR
DateCompare([Time].CurrentHierarchyMember.StartDate, Now()) < 0
) AND
( [Measures].[Time to resolution - Average hours],
[Time].CurrentHierarchyMember.Level.DateMember(
DateAdd(‘m’, -1, [Time].CurrentHierarchyMember.MiddleDate))
) <> 0
THEN
[Measures].[Time to resolution - Average hours] /
( [Measures].[Time to resolution - Average hours],
[Time].CurrentHierarchyMember.Level.DateMember(
DateAdd(‘m’, -1, [Time].CurrentHierarchyMember.MiddleDate))
) - 1
END

2

It seems you are using a default calculation Time ago > Month ago change %. This default calculation will always give you a comparison to the previous month no matter what you are using in the report, months, weeks (a week month ago), or days (same day month ago).

You can update the same code using ‘ww’ instead of ‘m’ as a parameter for function DateAdd to get it working for weeks. It will work correctly for Weeks and Days:

CASE WHEN (NOT IsEmpty([Measures].[Time to resolution - Average hours]) OR
DateCompare([Time].CurrentHierarchyMember.StartDate, Now()) < 0
) AND
( [Measures].[Time to resolution - Average hours],
[Time].CurrentHierarchyMember.Level.DateMember(
DateAdd('ww', -1, [Time].CurrentHierarchyMember.MiddleDate))
) <> 0
THEN
[Measures].[Time to resolution - Average hours] /
( [Measures].[Time to resolution - Average hours],
[Time].CurrentHierarchyMember.Level.DateMember(
DateAdd('ww', -1, [Time].CurrentHierarchyMember.MiddleDate))
) - 1
END

You can also check a set of options Time ago > Previous period, Time ago > Previous period change, and Time ago > Previous period change %. They work dynamically based on the selected Time level of your report. If the report is on weeks, then the previous period will be a week, if a report is on days, previous periods will be days, etc.

Daina / support@eazybi.com