YOY Measures based on custom calendar (Day Of Week aligned)

[Anonymous]

Hi All, 

I have a need to create a range of measures using YOY dates using a custom calendar where days of week always align. This is a seasonal tourism business which is extremely weekend dominant. If i use the built in SAMEPERIODLASTYEAR or PARALLELPERIOD I will end up comparing like this (Just a random date selection based on 19th July). 

Sun  2015-07-19
Tue  2016-07-19
Wed 2017-07-19
Thu  2018-07-19
Fri   2019-07-19

In the Date Table I have a DayOfYear which aligns the years correctly like this (Day of year = 200). 

Sun  2015-07-19
Tue  2016-07-19
Wed 2017-07-19
Thu  2018-07-19
Fri    2019-07-19

Here is an example of one of the measures I have created is here but I have a couple of questions

1. The formula is not respecting the dayofyear part of the filter, its returning full year, why?
2. Im not even sure that this is the most efficient methodology to peform YOY  calcs with this day of year allignment, is there a more efficient way meet this requirement?

PYTotalSalesYTD:=
IF(
    HASONEVALUE(TransactionDate[YearNumber]),
    CALCULATE(
        SUM(FactTransactionProduct[HomeGrossAmount]),
        FILTER(
            ALL(TransactionDate),
            TransactionDate[YearNumber]=VALUES(transactiondate[YearNumber])-1
                && TransactionDate[DayOfYear]<=max(TransactionDate[DayOfYear])
            )
        ),
        BLANK()
)

[OwenAuger]

Hi @Anonymous 

I would consider a structure like this for your PYTotalSalesYTD measure:

PYTotalSalesYTD :=
IF (
    HASONEVALUE ( TransactionDate[YearNumber] ),
    VAR CurrentYear =
        SELECTEDVALUE ( TransactionDate[YearNumber] )
    VAR MaxDayOfYear =
        MAX ( TransactionDate[DayOfYear] )
    VAR YearDayFilter =
        TREATAS (
            GENERATE ( { CurrentYear - 1 }, GENERATESERIES ( 1, MaxDayOfYear ) ),
            TransactionDate[YearNumber],
            TransactionDate[DayOfYear]
        )
    RETURN
        CALCULATE (
            SUM ( FactTransactionProduct[HomeGrossAmount] ),
            ALL ( TransactionDate ),
            YearDayFilter
        ),
    BLANK ()
)

I've split the logic into a few variables and YearDayFilter is the final filter applied to YearNumber & DayOfYear. This should be reasonbaly efficient as it just filters the required columns of TransactionDate and uses GENERATESERIES to create the list of DayOfYear values.

Does this give the correct result?

Regards,

Owen

[Anonymous]

Thanks so much to both of you, my first time posting was well worth it!

I have a feeling both solutions would work but @v-lili6-msft solution worked immediatelly. @OwenAuger , the measure errored which i'm sure is just a result of typing a measure in the absence of any actual data (Error was "Function GENERATE does not allow two columns with the same name "[VALUE]). I assume i need to get an alias into the formula somehow. The concepts in that measure blew my mind, ive been googling tring to fix the error and understand the structure of the YearDayFilter variable. I didn't realise you could assign a filter to a variable and didn't know about GENERATE or GENERATESERIES. 

Do you think the general concept of using the YearNumber and custom DayOfYear an acceptably efficient solution to the problem I have?

[OwenAuger]

@Anonymous 

My mistake - the two tables within GENERATE each had a column named Value.

Correction below 🙂

PYTotalSalesYTD :=
VAR CurrentYear =
    SELECTEDVALUE ( TransactionDate[YearNumber] )
RETURN
    IF (
        NOT ISBLANK ( CurrentYear ),
        VAR MaxDayOfYear =
            MAX ( TransactionDate[DayOfYear] )
        VAR YearDayFilter =
            TREATAS (
                GENERATE (
                    SELECTCOLUMNS ( { CurrentYear - 1 }, "YearFilter", [Value] ),
                    SELECTCOLUMNS ( GENERATESERIES ( 1, MaxDayOfYear ), "DayOfYearFilter", [Value] )
                ),
                TransactionDate[YearNumber],
                TransactionDate[DayOfYear]
            )
        RETURN
            CALCULATE (
                SUM ( FactTransactionProduct[HomeGrossAmount] ),
                ALL ( TransactionDate ),
                YearDayFilter
            ),
        BLANK ()
    )

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

1. Try to change ALL(TransactionDate) to ALLSELECTED(TransactionDate)

2. If you could try this formula as below:

PYTotalSalesYTD :=
IF (
    HASONEVALUE ( TransactionDate[YearNumber] ),
    CALCULATE (
        SUM ( FactTransactionProduct[HomeGrossAmount] ),
        FILTER (
            ALLSELECTED ( TransactionDate ),
            CONTAINS (
                VALUES ( TransactionDate[DayOfYear] ),
                TransactionDate[DayOfYear], DATE ( YEAR ( TransactionDate[DayOfYear] ) - 1, MONTH ( TransactionDate[DayOfYear] ), DAY ( TransactionDate[DayOfYear] ) )
            )
        )
    ),
    BLANK ()
)

If it is not your case, please share pbix file or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin