SSAS compare 2 YTD values for same month in different years

Question

I am developing a cube in SSAS, I need to be able to compare YTD values between current year and last year.

I have the below scenario:

Budget types by year : budget1-2016 , budget2-2016 ,budget1-2017 , budget2-2017.
The budget types should be placed in the columns
Units: should be placed in the rows
The measure is called "Billings"

I need to compare YTD value for "Billings" between budgets "budget1 - 2016" and "budget2-2017" (different budget types and year) for a specific month.

when the Calendar month is included in the rows or columns everything works fine,

but if it is included in the filter and I choose for example February 2016 and February 2017, the results are not as expected.

With the usage of the TAIL function, only the higher month is taken into consideration and the column from 2016 is removed.

Is there a way to put the calendar month in the filter, select same month from different years and be able to see their YTD values?

I am using an expression for the measure : _Billings = _Billings*Rate . The YTD value is calculated as follows : Sum(PeriodsToDate([Data Date].[Calendar].[Calendar Year],[Data Date].[Calendar].CURRENTMEMBER),[Measures].[_Billings]) — S.Najjar

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-04-12T11:08:51

The problem is that you put two members in the filter. CurrentMember doesn't work with two tuples. Hence [Data Date].[Calendar].CurrentMember is [Data Date].[Calendar].DefaultMember is [Data Date].[Calendar].[All]. It runs the following code:

Sum(
    PeriodsToDate(
        [Data Date].[Calendar].[Calendar Year],
        [Data Date].[Calendar].[All]
    ),
    [Measures].[_Billings]
)

You are choosing two month from different year, thus it's unclear what do you want to return from the PeriodsToDate() function. The data since January 2016 or January 2017? Choosing one of the members will fix your problem and make it clear.

I don't guarantee that's the perfect code to work with, but it may does the magic as well, add yet another calculated members to sum months:

Sum(
    existing [Data Date].[Month].[Month].Members,
    [Measures].[Billings]
)

SSAS compare 2 YTD values for same month in different years

1 Answers