cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Eyre Frequent Visitor
Frequent Visitor

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

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()
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

hi, @Eyre 

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

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

hi, @Eyre 

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

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

Hi @Eyre 

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Eyre Frequent Visitor
Frequent Visitor

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

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?

Super User
Super User

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

@Eyre 

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

Correction below Smiley Happy

 

 

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 ()
    )

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!