SAMEPERIODLASTYEAR issues Direct Query

[nleuck]

Hello All,

I'm having some trouble with the function SAMEPERIODLASTYEAR and slicer not displaying. I went through a few different stages and thought I would explain them to show you how I got to where I am now. The last stage is where I need help with this issue.

First I created a measure to show sales of the same period last year (January 2019 - March 2019).

Same Period LY Sales = CALCULATE(SUM(Table[Sales]), SAMEPERIODLASTYEAR(Table[OrderDate]))

Everything works fine, but when I select a manager from my slicer I get an error message along the lines of "...expects a contiguous selection when the date column is not unique...". After reading a few articles I came across this one that was selected as a solution:

http://www.kasperonbi.com/get-the-ytd-of-same-period-last-year-using-dax/ 

So I decided to test this out and create a new test measure.

Measure = CALCULATE(SUM(Table[Sales]), 

                           DATESBETWEEN(Calendar[Date], 

                               FIRSTDATE(DATEADD(Calendar[Date], -12, MONTH)),

                               LASTDATE(DATEADD(Table[OrderDate], -12, MONTH))

                               )

                             )

And it works as it expected. But once again when I selected a manager from my slicer I get the same error message.

Then, I tried creating another test measure.

Measure 2 = CALCULATE(SUM(Table[Sales]), SAMEPERIODLASTYEAR(Calendar[Date]))

The problem with this though is I am getting all of last year's sales and not the same period as this year (I would expect to get January 2019 - March 2019). But when I select a manager from my slicer my visual work just fine. The only thing wrong is I'm not getting the correct amount from my sales.

Any help with this would be greatly appreciated.

Thanks!

[nleuck · Accepted solution]

I found a solution that seems to work.

In my date calendar I create a flag for future date column (below the formula is a video that was very helpful) :

Future Date Flag = IF(Calendar[Date] > TODAY(), "Future", "Past")

https://www.youtube.com/watch?v=XjVLaVLluYE&list=PL7GQQXV5Z8eczWqKFMDVoHMjTcpH3tgZm&index=3 

I applied that as a filter for my visual card for Same Period Last Year Sales and only select "Past" (below is my measure for the value that the card displays):

Same Period Last Year = CALCULATE(SUM(Table[Sales]), SAMEPERIODLASTYEAR(Calendar[Date])

The visual works as expected even when I select a different manager. No more error...hopefully!

[amitchandak]

This should work with date calendar. As long as join and correct date it used.

Can you try datesytd or totalytd with date calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[nleuck]

@amitchandak 

I tried CALCULATE(SUM(Table[Sales]), DATESYTD(DATEADD(Calendar[Date], -1, YEAR), "12/31")) and as I suspected I got all of last year's sales and not last year's sales of the current period (January - March).

[amitchandak]

Seem like you have not selected any date .

Refer these

-- Force today filter
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=date(year(today()-1),month(today()),day(today())))

-- with filter

YTD Sales 1= CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Last YTD Sales 1 = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

--combine

final YTD = if(isfiltered(Date[Month Year]) || isfiltered(Date[Year]) || isfiltered(Date[Date]),[YTD Sales 1],[YTD Sales])
final LYTD = if(isfiltered(Date[Month Year]) || isfiltered(Date[Year]) || isfiltered(Date[Date]),[Last YTD Sales 1],[Last YTD Sales])

[nleuck]

@amitchandak 

These measures only work if I have a date slicer (ex. month_year). That kind of defeats the purpose for me. I want to display YTD sales (January 1st, 2020 - March 8th, 2020) and Last Year YTD sales (January 1st, 2019 - March 8th, 2019).

[amitchandak]

Something needs to tell time intelligence where to stop. This can be a slicer. Stop your calendar today. Have a visual level filter <= today.

I think the first two should have worked without any date filter

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=date(year(today()-1),month(today()),day(today())))