Calculation Group Item for YTD not changing results for a particular SELECTEDMEASURE

[Simon_Loughnane]

I have a monthly model with all of the data aggregated by month. The model uses a month level date table.

Since there are a lot of measures I am using a Calculation Group for my time intelligence to provide MTD, YTD and PY.

The calculation item for YTD is written as:

VAR LastYearAvailable = MAX('Dates'[Year])
VAR LastMonthAvailable = MAX('Dates'[Year Month Sequence])
VAR Result =
    CALCULATE(
        SELECTEDMEASURE(),
        REMOVEFILTERS('Dates'),
        'Dates'[Year Month Sequence] <= LastMonthAvailable,
         'Dates'[Year] = LastYearAvailable
   )
RETURN
Result

This works well and return the correct results in almost all instances.

However for one measure the YTD calculation item does not change the measure results.

The measure itself uses YTD numbers as its basis (I am spreading numbers over the coming year by diving the YTD number by 12). The measure code is:

Measure = 
VAR SelectedReportingYear = MAX(Dates[Year])
VAR MaxAvailablePeriod = MAX(Dates[Year Month Sequence])
RETURN
DIVIDE(
    CALCULATE(
        [GWP Plan],
        REMOVEFILTERS(Dates),
        Dates[Year] = SelectedReportingYear,
        Dates[Year Month Sequence] <= MaxAvailablePeriod
    ) , 
12
)

I would like the YTD Calculation Item to work on this measure in the usual way and aggregate the numbers month by month.

I guess that the Date filters in the Calculation Item and the Measure itself provide a matching filter context so both return the same numbers.

I have tried to rewrite my measure in different ways, including creating a VAR table of monthly values and then performing SUMX over it but the results are always the same.

Any advice would be very much appreciated.

Simon

[Anonymous]

A measure that works ALMOST all the time is a measure that does not work. The fact that your data is on the monthly level does not mean at all you should ignore a proper date table with the granularity of the day. In fact, you should indeed have a proper date table in the model, maybe with certain columns you don't need in the UI hidden (but they better be there, especially the Date column). The Date column needs to be present (even if not exposed to the end user) to do proper time-based calculations. If you have such a table, then your YTD measure/calc item is as easy as CALCULATE( SELECTEDMEASURE() , DATESYTD( Dates[Date] ) ).

[Simon_Loughnane]

Hi Daxer, thanks for your response. I agree with your recommended best practise of including a standard Date table in models. In this case I am following the 'Month Related Calculations' pattern described in SQLBI's DAX Patterns book. In this pattern the Date table is at the month level as are all of the other tables. You cannot then rely on the built in time intelligence functions but there are improvements over performance. Since my report users are only interested in month level data I have adopted this pattern in this case.

If I reverted back to a daily date table and used the DATESYTD function I believe I would still have the same problem. I will test it though to see if it might provide a solution.

Many thanks

Simon

[Anonymous]

@Simon_Loughnane 

Hiya. I was not aware that the setup you have follows a pattern from www.sqlbi.com. As much as I respect the work of Alberto and Marco I would in this case really recommend having a proper date table with a granularity of the day. You can then use the very first day of the month to join to your monthly level data end only expose the monthly granularity entities from the date table. But internally you should be using Date to make time-intel calculations (and it can't get easier than that). This way you've done yourself a big favor. 1) You'll be able to use all the time-intel functions without a hitch and 2) you'll have prepared yourself for any future changes to granularities and - most importantly - 3) it'll all work like a charm without much thinking.