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

Same days last year - discontinous period

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.

discontinous period.PNGdiscontinuos slicer

Have you any ideas?

Thank you so much.

Ester

2 REPLIES 2
Community Support Team
Community Support Team

Re: Same days last year - discontinous period

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
esterbluskova Frequent Visitor
Frequent Visitor

Re: Same days last year - discontinous period

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