locked
Filter for specific periods RRS feed

  • Question

  • I have 4 years of information. I would like to compare periods 8 months back from my last date in current year and then prior year 8 months back for all the rest of the years. How can I do this? 
    Monday, September 12, 2016 6:20 PM

Answers

  • Hi Albo44,

    I am trying to reproduce your scenario in my sample data and get the expected results as follows.

    First,  your base calculation is [Sales] (EG: SUM(table[sales])), calculating the total sales over the period selected is

    SaleInperiod:=CALCULATE(SUM(Cost[sale]),DATESINPERIOD(Cost[date],MAX(Cost[date]),-1*8,MONTH))

    The same period calculation one year earlier is  

    SalesInPeriodLastYear:=CALCULATE([SaleInperiod],SAMEPERIODLASTYEAR(Cost[date]))

    Compare periods 8 months back from my last date in current year to prior year 8 is

    comparison:=[SaleInperiod]/[SalesInPeriodLastYear]

    Then, you create a pivot table as follows.


    For reference: https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/.

     If this is not what you what, please give us more details and sample data structure.



    Regards,
    Angelia

    Tuesday, September 20, 2016 9:56 AM