Show year to date sales for previous years

Question

I am making a report that counts the amounts of offers, uniquely identified with SALE_ID, containing data from different products starting first of January 2015 ranging up to todays date (18/12/2017 at the time of asking). I am counting the amounts of offers with a simple measure called 'Distinct':

Distinct := DISTINCTCOUNT(dOffers[Sale_ID])

This gives me satisfactory results, as in, I am receiving the right counts for the considered period. I am also calculating year-over-year changes, defining the previous year offers with the following measure: (dCalendar contains the datekey table).

    PY Offers :=
SUMX (
    VALUES ( dCalender[YearMonthNumber] );
    IF (
        CALCULATE ( COUNTROWS ( VALUES ( dCalender[FullDates] ) ) )
            = CALCULATE ( VALUES ( dCalender[MonthDays] ) );
        CALCULATE (
            [Distinct];
            ALL ( dCalender );
            FILTER (
                ALL ( dCalender[YearMonthNumber] );
                dCalender[YearMonthNumber]
                    = EARLIER ( dCalender[YearMonthNumber] ) - 12
            )
        );
        CALCULATE (
            [Distinct];
            ALL ( dCalender );
            CALCULATETABLE ( VALUES ( dCalender[MonthDayNumber] ) );
            FILTER (
                ALL ( dCalender[YearMonthNumber] );
                dCalender[YearMonthNumber]
                    = EARLIER ( dCalender[YearMonthNumber] ) - 12
            )
        )
    )
)

The problem that I am having, is that the year-over-year change for the month december (the running month), considers the year-to-date sales for this year (2017) and compares this to full month sales in the previous years (2016 and 2015); this makes the last months comparison uninterpretable, as we are comparing offers from half a month to offers from a full month.

I would like to know how to fix this problem: i.e. consider the sales for the full year up to todays date, and compare this for the exact same periods last year and two years ago (2015: start Jan 1st and go up to Dec 18th; idem dito for 2016 and 2017). The SAMEPERIODLASTYEAR call might seem straightforward forthis issue, but I am receiving a contiguous dates errors...

Thanks in advance!

DAX0110 DAX0110 · Accepted Answer · 2017-12-19T15:48:40

From your description, I understand that you're trying to perform year over year month to date comparisons.

Without relying on any time intelligence functions like SAMEPERIODLASTYEAR, you may want to try this version of the measure:

PY Offers :=
SUMX(
    VALUES( dCalendar[YearMonthNumber] );

    CALCULATE(
    VAR currDate = MAX( dCalendar[FullDates] )
    VAR currYear = YEAR(currDate)
    VAR currMonth = MONTH(currDate)
    VAR currDay = DAY(currDate) 
    RETURN
        CALCULATE(
            [Distinct];
            ALL( dCalendar );
            YEAR(dCalendar[FullDates]) = currYear - 1;
            MONTH(dCalendar[FullDates]) = currMonth;
            DAY(dCalendar[FullDates]) <= currDay
        )
    )
)

Show year to date sales for previous years

1 Answers