Give some examples of measures we can create using the Window Function.

How to create a rolling 12 months average?

Window functions can be utilized to create measures for running totals, rolling totals, month-to-date, quarter-to-date, year-to-date, averages, and many other calculations.

For example, a Rolling 30 measure can be created using window functions.

Rolling 7 = CALCULATE([Net], WINDOW(-7,REL,0,REL, 
                                    ALLSELECTED('Date'), 
                                    ORDERBY('Date'[Date],ASC))

Since month-year text cannot be sorted directly, a sort column must be used in the formula to create a rolling month measure.

Rolling 12 Month

Rolling 12 Month = 
CALCULATE(
    [Net], 
    WINDOW(
        -11, 
        REL, 
        0, 
        REL, 
        ALLSELECTED('Date'[Month Year sort], 'Date'[Month Year]), 
        ORDERBY('Date'[Month Year sort])
    )
)

Average of Rolling 12 months

Rolling 12 Month Avg = 
CALCULATE(
    AVERAGEX(VALUES('Date'[Month Year]), [Net]), 
    WINDOW(
        -11, 
        REL, 
        0, 
        REL, 
        ALLSELECTED('Date'[Month Year sort], 'Date'[Month Year]), 
        ORDERBY('Date'[Month Year sort])
    )
)

This formula calculates the rolling 12-month average by using the AVERAGEX function over the distinct months, with the WINDOW function determining the relevant range and sorting.

For running total or cumulative calculations, we need to start from the absolute 0/1 position. In this approach, I am developing the formula with the date. By the date, it will be possible to compute cumulating values on various granularities: week, month, quarter, year.

Date Wise running total

Cumulative Total = 
CALCULATE(
    [Net], 
    WINDOW(
        1, 
        ABS, 
        0, 
        REL, 
        ALLSELECTED('Date'), 
        ORDERBY('Date'[Date])

Now, if we control this cumulative formula using partitions, we can get MTD, QTD, and YTD

MTD = CALCULATE([Net], WINDOW(1,ABS,0,REL, 
                                    ALLSELECTED('Date'), 
                                    ORDERBY('Date'[Date]),,PARTITIONBY('Date'[Month Year])
                    ))

QTD = CALCULATE([Net], WINDOW(1,ABS,0,REL, 
                                    ALLSELECTED('Date'), 
                                    ORDERBY('Date'[Date]),,PARTITIONBY('Date'[Qtr Year])
                    ))

YTD = CALCULATE([Net], WINDOW(1,ABS,0,REL, 
                                    ALLSELECTED('Date'), 
                                    ORDERBY('Date'[Date]),,PARTITIONBY('Date'[Year])
                    ))