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
Highlighted
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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 267 members 2,950 guests
Please welcome our newest community members: