cancel
Showing results for
Did you mean:
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

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

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

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

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

## 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

Proud to be a Datanaut!

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

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

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