Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Trailing 3 months

Do you know how to create a dynamic measure for trailing 3 months, based upon a slicer date range?   For example, in November, it would calculate Aug, Sept, Oct.....     and in Dec, it would calculate Sept, Oct, Nov.

General Comment

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Sure, it's going to be a variation on my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

     

    Essentially, create a VAR that is MAX([Month]) - 1. This is your selected month and it should be numeric. Then you create another VAR that is that variable minus 2. Then it is a simple matter of filtering ALL of your table where the numeric month is between those two values.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you so much Greg!  This worked perfectly!

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Here is a test pbix  you may refer to

     

    create a calendar date table, don't connect this date table to your table, this calendar date table would automatically generate the date according to your tables.

    Table = CALENDARAUTO()

    create "year" and "month" column inside this calendar date table

    year = YEAR([Date])

month = MONTH([Date])

    Then add [year] and [month] column in the slicers

     

    Next create measures in your table

    Measure =
CALCULATE (
    SUM ( Sheet1[values] ),
    FILTER (
        Sheet1,
        YEAR ( [date] ) = SELECTEDVALUE ( 'Table'[year] )
            && MONTH ( [date] ) < SELECTEDVALUE ( 'Table'[month] )
            && MONTH ( [date] )
                >= SELECTEDVALUE ( 'Table'[month] ) - 3
    )
)

     

    Best Regards

    Maggie