Sparklines on Previous Month Filter doesn't pick up

Hi There,

I’m having issues with the sparklines when the “previous month” default filter is selected.

It works when I select the actual month:

But once I select previous month, the sparklines don’t pick up:
image

Any ideas on how we could resolve this issue?

Thanks

Hi @eli_solutions,

A specific month or a year is a member of the default hierarchy in the Time dimension, and it has a specific level and other months or years of the same hierarchy level.

Calculated members like “Current year” or “Current month” do not fit into any hierarchy. Therefore they do not have previous periods.

The solution lies in finding the hierarchy level and the current member from which to count previous periods.
The expression for the sparkline for 6 previous periods for the current or previous year, quarter, month, day, or week might look like this.

SparklineData(
  LastPeriods(6,
    IIf([Time].CurrentHierarchyMember IS
        [Time].CurrentHierarchy.DefaultMember,
    [Time].[Month].CurrentDateMember,
--if calculated member    
      iif([Time].CurrentHierarchyMember.Level.Name = "(All)",
--take its first child to find the hierarchy and the level       
   ChildrenSet([Time].CurrentHierarchyMember).Item(0),
--if not a calculated member - take itself
      [Time].CurrentHierarchyMember))
  ),
  [Measures].[<your measure>]
)

Please put the correct measure name instead of <your measure>.

The expression first checks if the Time dimension (All Times) member is used and then checks if the selected Time dimension member belongs to a hierarchy. If a member of the hierarchy applies, it could be used without modifications. Otherwise - if a calculated member is used - the expression takes its first child as a source member for the calculation of previous periods.

You might apply the “Sparkline-line” formatting to the measure to display the sparkline.

Regards,
Oskars / support@eazyBI.com

1 Like

Thanks for all the details.