Trailing 3 months

[Anonymous]

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.

[v-juanli-msft]

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

[Greg_Deckler · Accepted solution]

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-TIT...

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]

Thank you so much Greg!  This worked perfectly!