locked
Year to Date RRS feed

  • Question

  • Is there an easy way to show year to date sales and the prior year to date sales for the same time period? I would need this to dynamically change from month to month.

     

    For example, 2011 now has sales through June, so I would need to show.

    June 2011 Sales, June 2010 Sales, 2011 Total Sales Jan-June, 2010 Total Sales Jan-June


    Kim B.
    Monday, June 27, 2011 6:36 PM

Answers

  • You should be free to filter at any level. The YTD formulas should work for day or month, at year or total level they simply return full sum. The sum formulas should work at all granularities. Can you show or explain what was not right for you when you didn't filter to a day?

    Thursday, July 14, 2011 8:54 PM

All replies

  • The easiest way is to use DAX time intelligence functions. You need a seperate Time dimension table that is related to the Sales fact table in order to use time intelligence functions. Assume your two tables are named FactSales and DimDate respectively, you can create four seperate measures and add them to your pivot table

    [m1] = Sum(FactSales[SalesAmount])

    [m2] = [m1](SamePeriodLastYear(DimDate[DateKey]), All(DimDate))

    [m3] = TotalYTD([m1], DimDate[DateKey], All(DimDate))

    [m4] = TotalYTD([m1], DATEADD(DimDate[DateKey], -1 YEAR), ALL(DimDate))

     

     

    Monday, June 27, 2011 7:11 PM
  • Here is what I have for my measures. The first one comes out correctly. The second one comes out with the same number rather than YTD. The second and third don't come back with anything. Do I have the syntax incorrect?

    Measure 1=Sum(Fct_Sales[SalesAmt])

    Measure 2=[Measure 1](SAMEPERIODLASTYEAR(Dim_Time[Date]), All(Dim_Time))

    Measure 3=TOTALYTD([Measure 1],Dim_Time[Date],All(Dim_Time))

    Measure 4=TOTALYTD([Measure 1],DATEADD(Dim_Time[Date],-1 ,YEAR),ALL(Dim_Time))


    Kim B.
    Wednesday, June 29, 2011 2:46 PM
  • Hi Kimberly

    The second one should be the same period for last year, not YTD correct?

    Just to test, can you add a calculated column in your Dim_Time table with the expression =SamePeriodLastYear(Dim_Time[Date]).  You should get for every row, the last year's date compared to the date in the current row (for each row)




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Wednesday, June 29, 2011 5:28 PM
    Answerer
  • Check a couple of common mistakes people make:

    1. Add columns from Dim_Time table to pivot table row/column labels, not the date related columns from Fct_Sales.

    2. Make sure Dim_Time contains all dates without any gaps in the years where you have sales data.

    3. Make sure you have a relationship from Fct_Sales to Dim_Time.

     

    Wednesday, June 29, 2011 9:57 PM
  • Thank you Jeffey and Javier. I am assuming that I have a scoping issue. When I filter on the report and use DimTime and filter on a specific day, then these calculations work for me. But it doesnt seem right that I would have to filter to a day. That is the part that is confusing me. Is it necessary to only put one specific calendar day on the filter to get these caclucations to work, or do i have to change something in the formula to provide different scoping? Can you provide me with what would be put in the Report Filter and Column Labels? For Example If I wanted to show a store and the sales for this year and then the sales for the prior year 2 years but I only wanted the amounts for the prior 2 years to be YTD.

    Store A  2011 YTD, 2010 Same Time Period, 2009 Same Time Period

     

    Thanks,

    Kim


    Kim B.
    Thursday, July 14, 2011 2:32 PM
  • You should be free to filter at any level. The YTD formulas should work for day or month, at year or total level they simply return full sum. The sum formulas should work at all granularities. Can you show or explain what was not right for you when you didn't filter to a day?

    Thursday, July 14, 2011 8:54 PM