Compare sales with different festive dates every year

[naufal_msr]

Hi,

Im looking for ways to compare sales during festivities. What I've done so far is create a custom festive calendar and link to my calendar table. I guess the issue is currently on festivities that do not fall on the same date every year like Christmas / New Year. For example Chinese Lunar New Year falls on different date and Im not sure how to do in DAX. Purpose of me doing this is I wanna create a calculated column for Last Year Holiday Sales which ultimately i wanna compare the growth etc. If i do thisyearsales - PREVIOUSYEAR(sales) it will give me the same date of this year which is not accurate the date of last year's festivity. Not sure if this makes any sense but I appreciate any help I can get. 

Thanks !

[v-rzhou-msft · Accepted solution]

Hi @naufal_msr ,

Firstly I suggest you to add an unrelated Holiday table with all holidays and dates you need in it. 

Then create a calendar table with holiday from this Holiday table.

Date = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Holiday",
        CALCULATE (
            MAX ( 'Holiday Date'[Holiday] ),
            FILTER ( 'Holiday Date', [Date] >= [Holiday Start] && [Date] <= [Holiday End] )
        ),
    "YEAR", YEAR ( [Date] )
)

Relationship:

Measures:

Cur Year Sales =
VAR _SELECTHOLIDAY =
    SELECTEDVALUE ( 'Holiday Date'[Holiday] )
VAR _SALES =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Date', 'Date'[Holiday] = _SELECTHOLIDAY )
    )
RETURN
    _SALES

Previous Year Sales =
VAR _SELECTHOLIDAY =
    SELECTEDVALUE ( 'Holiday Date'[Holiday] )
VAR _SALES =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        PREVIOUSYEAR ( 'Date'[Date] ),
        'Date'[Holiday] = _SELECTHOLIDAY
    )
RETURN
    _SALES

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@naufal_msr , You have to give the period a name of a number in the date calendar and then

example

This year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Name] = "New Year" ))
Last year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Name] = "New Year"))

[naufal_msr]

hi,

thank you for your help. However, Im having issue to highlight the YEAR column. Any idea why ?

[amitchandak]

@naufal_msr , In filter inside all, Take only table name. When you take column name, it restricts it to that column

Filter(all(CalendarTable) ,

[naufal_msr]

Thank you for the help !

If I have many holidays to be included, does it mean I need to create a DAX for each of the holidays in order for me to display by Sales by Holiday ?

[amitchandak]

@naufal_msr , You can try like

Last year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[PH] = max('Date'[PH] ) ))

this will club holiday type , now if you have PH is the context you can compare YOY for that PH