Another Previous Year value question


  • Hi

    I hope someone can help.  I am new to DAX, but have managed to solve one part of my problem.  To demonstrate the problem, I will use examples from Adventure Works.

    I need to calculate the sum(Sales Amount) for each period (format: yyyymm) of the last year.  The last year should be dynamic, determined by the last ORDER DATE in the Fact table (Internet Sales).  So in short I want the Total Sales amount per period for the last 12 months (Period Internet Sales ).  I got this part right with the following:

    CALCULATE(Sum('Internet Sales'[Sales Amount]),DATESINPERIOD('Internet Sales'[Order Date],

                                        CALCULATE(MAX('Internet Sales'[Order Date]),FILTER(ALL('Internet Sales'),TRUE)),-1,YEAR))

    Now I have the problem that I want to compare these period values with sales values from the 12 months prior to the last year.  This means that if I have a sales value for period 200807, I want to compare it to the sales value in 200707.  My problem is that I can get the values, but cannot restrict or filter it to the same periods.

    The prior period values (Internet Prior Period Sales  calculated by:

    CALCULATE(SUM('Internet Sales'[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

    How do I restrict INTERNET PRIOR PERIOD SALES to also just display for the same 12 periods as PERIOD INTERNET SALES?


    Thursday, July 11, 2013 9:14 AM


All replies