Same days last year - discontinous period

[esterbluskova]

Hello,

I would be glad for your help. I am solving the problem of calculating of last year's sales.

I have 2 tables:

Sales table:

Date, ShopNumber, EAN, Turnover,

CalendarTable:

Date, Month

My calculation of SUM of turnover is now:

Sum Turnover Last Year :=
CALCULATE (
    SUM ( [Turnover] );
    DATESBETWEEN (
        'CalendarTable'[Date];
        [FIRSTDATELASTYEAR];
        [LASTDAYLASTYEAR]
    )
)

BUT, when someone choose on slicer discontinuos period, my calculation is wrong.

discontinuos slicer

Have you any ideas?

Thank you so much.

Ester

[v-yulgu-msft]

Hi @esterbluskova,

Based on my assumption, these existing a relationship between Sales table and CalendarTable. [FIRSTDATELASTYEAR] and [LASTDAYLASTYEAR] are two measures return last year dates dynamically based on slicer selection, right?

I would suggest you remove the table relationship. Then, add a last year dates column in calendar table.

LY = DATEADD('calendar'[Date].[Date],-1,YEAR)

FIRSTDATELASTYEAR= MIN('calendar'[LY])

LASTDAYLASTYEAR= MAX('calendar'[LY])

Sum Turnover Last Year =
CALCULATE (
    SUM ( [Turnover] ),
    FILTER (
        'Sales Table',
        'Sales Table'[Date] >= [FIRSTDATELASTYEAR]
            && 'Sales Table'[Date] <= [LASTDAYLASTYEAR]
    )
)

Best regards,

Yuliana Gu

[esterbluskova]

Hi Yuliana,

Thank you for your reply. I try your calculation and it works great. But it has same problem as my calculation.

For example. If my turnover is every day same: 100 Eur. and I choose on slicer: 2 days: 2.1.2018 and 4.1.2018.

Correct calculation is 200 Eur, but now it show me 300 Eur. (it incude 3.1.2018)

It is caused by filter:

        'Sales Table'[Date] >= [FIRSTDATELASTYEAR]
            && 'Sales Table'[Date] <= [LASTDAYLASTYEAR]

And the question is how to create filter, which select same days as in slicer.

Thank you.

Best Regars

Ester