Forum Discussion

umpoohg's avatar
umpoohg
Helper I
9 years ago
Solved

SAMEPERIODLASTYEAR WITH YTD

Hello all,   I am fairly new to Power BI, and I have been tasked with creating some Sales report, specifically a report with the YTD, MTD, QTD and well as the sameperiodlastyear for each.  I have c...
Need Help
  • jblackshear's avatar
    jblackshear
    9 years ago

    (I'm still pretty new at this myself but I encountered this situation)

    I think your previous YTD is using the same context as the current YTD, or maybe you have a date filter on the whole matrix to report a specific year, so it isn't finding any data. Here is the thread with the problem I had and the solution.

     

    SAMEPERIODLASTYEAR with a year filter

     

    What I ended up doing was this (this is from my own solution, I haven't tried to fit it to yours)

    RevenueLastYTD = CALCULATE([RevenueYTD], FILTER(ALL(Dates), Dates[CalendarYear]=MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))

     

    The key is using CALCULATE and FILTER. FILTER(ALL(Dates), ...) first opens up the dates context to use all dates again (because it may be currently set at a specific year, either because of a date filter on the whole matrix, or because of the row it is on). Then the next part of the filter statement sets a new filter for the previous year. I'm not entirely sure if that part is necessary since I'm calling SAMEPERIODLASTYEAR. I first did this several months ago and haven't worked with it much since then.

     

    But take a look at using FILTER to open up the context in your previous year calculation.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi umpoohg,

Do you use the following formula to create [Total Sales]?

[Total Sales] = SUM(IHeads[Sales])

If that is the case, use the following formula to create YTD sales. After that, use date columns from DateTable and “YTD Sales”, “same period last year sameperiodlastyear” measures from IHeads table to create visual , then check if the measures return expected result.

YTD Sales = TOTALYTD(SUM(IHeads[Sales]), DateTable[Date])

There is an example for your reference.

 

Thanks,
Lydia Zhang

umpoohg's avatar
umpoohg
Helper I
9 years ago

Hi Lydia,

 

The problem that I am really having is I can get year to date, however I am unable to get the sameperiodlastyear to work.  

 

 

Prev Prd Sales = CALCULATE(SUM('IHeads'[Sales]), SAMEPERIODLASTYEAR(DateTable[Date]))

Prev Prd Sales = CALCULATE(SUM('IHeads'[Sales]), SAMEPERIODLASTYEAR(DateTable[Date]))

 

Both methods still bring in the same exact values.  The amount shown is the total sales for 2016, so that is not exactly right.  I am just looking for YTD or (01/01/2017 - 02/27/2017) and then beside that the sameperiodlastyear or (01/01/2016 - 02/27/2016), as shown above.

 

Any assistance would be appreciated.

 

 

 

 

  • Datatouille's avatar
    Datatouille
    Solution Sage
    9 years ago

    Hi umpoohg

     

    Try these measures:

     

    Sales YTD = Calculate ( SUM('IHeads'[Sales] ) , DatesYtd( DateTable[Date] ) )

     

    Sales YTD LY = Calculate ( [Sales YTD] , SamePeriodLastYear( DateTable[Date] ) )

     

    You can also leverage DAX variables and embed them to compute Sales YTD LY:

     

    Sales YTD LY 2 = VAR YTDTab = DatesYtd(DateTable[Date]) VAR YTDLYTab = SamePeriodLastYear( YTDTab ) RETURN

    Calculate (  SUM('IHeads'[Sales]) , YTDLYTab )

    • umpoohg's avatar
      umpoohg
      Helper I
      9 years ago

      Hi Datatouille,

       

      Thank you for your response, however below are the results...so strange?!?!?!?!

       

      Sales YTD = Calculate ( SUM('IHeads'[Sales] ) , DatesYtd( DateTable[Date] ) )

       

      Sales YTD LY = Calculate ( [Sales YTD] , SamePeriodLastYear( DateTable[Date] ) )

       

       

      Seems it is basically doing what the measures I have created are doing...

       

       

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi umpoohg,

    I understand your scenario. Have you added year and month columns to your visual? You can review the screenshot in my first reply, sameperiodlastyear returns correct values.

    Check the following similar blogs to get details about which scenario sameperiodlastyear function applies to .

    https://bipassion.wordpress.com/2012/08/26/dax-sameperiodlastyear-and-datesytd/
    http://www.wiseowl.co.uk/blog/s2477/same-period-previous-year.htm

    Thanks,
    Lydia Zhang

    • umpoohg's avatar
      umpoohg
      Helper I
      9 years ago

      Hi Anonymous

       

      I have tried so many variations of this, it has to be related to my date table, in my opinion.   I just have not been able to tweak the table.  Do you mean add the month and year to the values or the grid, when I do that, it explodes the data, but it does show that the correct months are not being displayed.  I would expect to only see data from Jan and Feb of 2016 and 2017..

       

    • umpoohg's avatar
      umpoohg
      Helper I
      9 years ago

      Hi Anonymous

       

      I have tried so many variations of this, it has to be related to my date table, in my opinion.   I just have not been able to tweak the table.  Do you mean add the month and year to the values or the grid, when I do that, it explodes the data, but it does show that the correct months are not being displayed.  I would expect to only see data from Jan and Feb of 2016 and 2017..

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi umpoohg,

        Create a Matrix visual as shown in the following screenshot, then filter the years using a slicer based on your needs. But in this sceanrio, we are not able to filter month as SAMEPERIODLASTYEAR only works with contiguous date selections.



        Thanks,
        Lydia Zhang