cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
v-yulgu-msft Super Contributor
Super Contributor

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 49 members 1,211 guests
Please welcome our newest community members: