MDX Calculated Measure Year to Date

1
votes

I have an OLAP multi dimensional cube, which in its simplest form contains:

  • A fact table with a NetAmount measure
  • A Time dimension, with fields for Year, Month, Day, Day of Month, Day of Year, etc

The requirement is that the user wants to be able to compare the total NetAmount from the beginning of each year up to a specific date. The date can be chosen by setting filters on the Day and Month fields. For example, the user wants to see total value for:

  • 01 Jan 2019 to 20 Jan 2019
  • 01 Jan 2018 to 20 Jan 2018
  • 01 Jan 2017 to 20 Jan 2017
  • etc

What would be the best way to tackle this requirement?

1
ssasmdxolap

1 Answers

1
votes

You can create calculated member with YTD function like this:

CREATE MEMBER CURRENTCUBE.[Measures].[NetAmountYTD]
as

Aggregate(YTD ([Time].[Time].CurrentMember), [Measures].[NetAmount])

You could also create Utility dimension for example TimeUtility, and add YTD member, and define it like this:

Scope([TimeUtility].[YTD]);
    this = Aggregate(YTD ([Time].[Time].CurrentMember), [TimeUtility].[DefaultMember]);
End Scope;

This way whatever measure you look against [TimeUtility].[YTD] it will give you Year to Date for that measure, not just NetAmount, so it will be general solution.