Forum Discussion

jblackshear's avatar
jblackshear
Advocate III
9 years ago
Solved

SAMEPERIODLASTYEAR with a year filter

I am trying to display sales data for the current year, with a measure for the current month's sales, YTD sales together with last year's YTD. We only want to display a single year's data on this rep...
Need Help
  • jblackshear's avatar
    jblackshear
    9 years ago

    I wish I could accept both answers as the solution, because I appreciate your help in pointing me in the right direction.

     

    I had tried many different variations on your suggestions, with close-but-not-quite-right results.

     

    I thought that maybe the date intelligence functions were not working quite right with my date table, and decided to rebuild it. The dates in my data warehouse are integers of the form 20161121. Originally, I brought them in as is, then created a calculated column in Power BI to create the date field using the date parts (year, month, day stored in separate fields). Then I matched the tables up in the Power BI designer.

     

    This time I created the calculated date key in the SQL query, for both tables. Now when I use the normal date functions, they work as expected. It definitely helped to know the syntax that is supposed to work, so again I thank you both.

     

    What I ended up with for my RevenueLastYTD measure is:

     

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

     

    It correctly displays, for each month, the YTD for the same month of the previous year.

     

     

     

     

     

     

     

blopez11's avatar
blopez11
Super User
9 years ago

The following has worked for me in the past, assuming your date table is Dates, where CalcDate is of type Date:

 

RevenueYTD = CALCULATE(SUM (...))

RevenueLastYTD = CALCULATE([RevenueYTD], SAMEPERIODLASTYEAR(Dates[CalcDate]))

 

Then for like a clustered bar chart, have RevenueYTD and RevenueLastYTD in the Values area and Month from your date table in the Axis area, then as you mention use Year from your date table as a slicer

  • jblackshear's avatar
    jblackshear
    Advocate III
    9 years ago

    I appreciate your response, thank you.

     

    I tried out the measures as you describe, which are much like my first attempt shown above. 

     

    RevenueYTDv2 = CALCULATE([TotalRevenue], DATESYTD(Dates[CalcDate].[Date]))

     

    RevenueLastYTDv5 = CALCULATE([RevenueYTDv2], SAMEPERIODLASTYEAR(Dates[CalcDate].[Date]))

     

     

    I get the same results as with my previous attempt - when I add the year slicer, the previous YTD values disappear.

     

     

    • blopez11's avatar
      blopez11
      Super User
      9 years ago

      Have you tried removing the .[DATE] from Dates[CalcDate].[Date]?

      • jblackshear's avatar
        jblackshear
        Advocate III
        9 years ago

        I do not yet understand what the date column does with and without the [date], [year], etc. bits added on.

         

        But when I leave off [date], instead of getting year-to-date total I get only the total for that month.  

         

        I end up trying everything with both variations to see if one works and the other doesn't. I then use the least specific one that works. Not very precise or technical, I know. I hope some good understanding comes soon because I don't like working this way.