YTD this year VS SAMEPERIODLASTYEAR Sales

[avelio]

Dears,

I've been struggling for the past few days to come up with a formula which calculates SAMEPERIODLASTYEAR Visits.

So far i've come up with:

YTD = CALCULATE(SUM(Main[Visits]),DATESYTD(DateKey[Date].[Date]))

YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR(DateKey[Date].[Date]))

YTD - Work just fine!

The problem is YTD LY, it returns the sales for the whole year.

The values shouldnt be so far apart.

I'm also mentioning that i have a DateKey Table with tabes linked to my facts table through Date (1:1 relationship).

Getting this solved would be a huge deal for me.

Any tips?

BR,

Andrei

[Anonymous]

What happens when you add Dates column to your report table?

What values does YTD LY show now?

Please send a printscreen

[avelio]

@Anonymous seems like YTD LY calculates visits for the whole year. It shoud only calculate for the same period of this year according to the formula..

YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR(DateKey[Date].[Date]))

YTD = CALCULATE(SUM(Main[Visits]),DATESYTD(DateKey[Date].[Date]))

BR,

Andrei

[Anonymous]

Hi @avelio

I have recreated your formulae and they work just fine:

YTD LY for 201701 is 296 as is YTD for 201601

Please see the file attached:

https://1drv.ms/u/s!AoP_9ampPIT7jQIzMzSXbamGsno0

[avelio · Accepted solution]

@Anonymous thanks!

That i have figured it out myself. The problem is that i need to have these values without any date filter in the table.

If i simply put YTD and YTD LY in one table it doesn't work.

This printscreen is from you pbix file.

I did one thing though and it worked.

YTD =
IF (
MIN ( Main[Datee] ) <= CALCULATE ( MAX ( Main[Datee] ), ALL ( Main ) ),
CALCULATE ( SUM ( Main[Visits] ), DATESYTD ( Main[Datee] ) )

YTD LY (limited by last date in Data table) =
VAR DataMaxDate =
CALCULATE ( MAX ( Main[Datee] ), ALL ( Main ) )
RETURN
CALCULATE (
[YTD 2],
SAMEPERIODLASTYEAR (
INTERSECT (
VALUES ( DateKey[Date].[Date] ),
DATESBETWEEN ( DateKey[Date].[Date], BLANK (), DataMaxDate )
)
)
)

I've limited the date in my Main table to the last date i have values in it.

That's the only thing i found that works without using Date as filter (as you suggested)

BR,

Andrei