Forum Discussion

nleuck's avatar
nleuck
Post Patron
6 years ago
Solved

SAMEPERIODLASTYEAR issues Direct Query

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!

 

Need Help
  • nleuck's avatar
    nleuck
    6 years ago

    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!

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      nleuck
      Post Patron
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        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's avatar
    nleuck
    Post Patron
    6 years ago

    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!