[HELP] Compare Sales by Filtering an annual event of different time period

[Chris_YS]

Hello guys, I have been digging old posts and couldn't find a solution at this...

This is my dataset: 

I have set an event filter: e.g. Toy Show which is held annually BUT DIFFERENT Period of Date. (that's why sameperiodlastyear doesn't work)

My purpose is to compare: Rev of 2019 - 2018

However, I cannot use this formula to do so: Rev YOY:=[Rev]-[Rev-1]

While Rev-1:=CALCULATE([Rev],dateadd('Calendar'[Date], -1, year))

it shows an error:

This should be a simple straight thing... by substracting Rev2019 - Rev2018 of the Toy Show = -$14, 293

I can only compare with this imperfect formula: 

Rev YTD YOY-1:=[Rev]-[Rev_YTD-1]

While Rev_YTD-1:=if(hasonevalue('Calendar'[Year]), sumx(values('Calendar'[Month]), if([Rev]>0, calculate([Rev], DATEADD('Calendar'[Date], -1, YEAR)))))

The tricky part is that the Toy Show starts earlier in 2018 from 26/1/2018;

but start later in 2019 from 1/2/2019

So that Rev_YTD show a total of $1,336 (Sum of the green area) 

Many thanks in advance 

[V-lianl-msft]

Hi @Chris_YS ,

You can try to create a new calendar table, and then recreate the measure by comparing the date column of the calendar table with the actual date column.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Chris_YS 

You this formula is correct-While Rev-1:=CALCULATE([Rev],dateadd('Calendar'[Date], -1, year))

But seems like you have not create Calendar using Calendar function. You can not have a noncontinuous date in that case. Can you share how calendar is created

Refer this how calendar is created: https://www.dropbox.com/s/rxhq0ko80zejlxq/May2Apr-Cal_FY_calendar.pbix?dl=0