locked
Last 24 months RRS feed

  • Question

  • Hi !

    MY requirement is to display 24 months based on Slicers.

    For eg. in slicer if i select 201505. I want to display last 24 months data from 201505 in the Powerpivot report .

    Please help me on this.


    • Edited by kowssri Thursday, June 16, 2016 5:05 PM
    Thursday, June 16, 2016 5:05 PM

Answers

  • You'll need two separate calendar tables to do this. One is used to provide the period list for the slicer, the other one is used for the axis of your chart (or the labels in a pivot table). Your table containing the data to aggregate should have a relationship to both tables.

    Suppose you populate the slicer with Calendar1[Month], and the axis with Calendar2[Month], and your base result measure is [Results] (e.g. SUM(Data[Amount]). Create a new measure with the formula

    ResultLast24Months:= CALCULATE([Results],DATESINPERIOD(Calendar1[Date],MAX(Calendar1[Date]),-24,month))

    Displaying [ResultsLast24Months] against a Calendar1 axis will show the total of the last 24 months, but against a Calendar2 axis the results are split up against the months in Calendar2.

    • Proposed as answer by Charlie Liao Thursday, June 30, 2016 2:13 AM
    • Marked as answer by Charlie Liao Thursday, June 30, 2016 2:58 PM
    Friday, June 17, 2016 9:54 AM
    Answerer