cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BlueWhite111
Post Patron
Post Patron

12week rolling average

Hi Experts

 

need to calculate the 12 week rolling average see sample data - PS i have all the necessary columns in my DimDate Table, i want to plot the 12 week rolling average in a graph

sample Data

BlueWhite111_0-1669720767237.png

 

1 ACCEPTED SOLUTION
Mahesh0016
Solution Sage
Solution Sage

_12wk Average =
VAR Week = CALCULATE(DISTINCTCOUNT('Date'[Week]),FILTER(
        ALL('Date'),
        (
            (
                'Date'[Week]
                    >= MAX ('Date'[Week] ) - 11
            )
                && (
                    'Date'[Week]
                        <= MAX ( 'Date'[Week] ) - 0
                )
        )
    ))
VAR Total=CALCULATE(SUM('Table'[Sales]),
    FILTER(
        ALL('Date'),
        (
            (
                'Date'[Week]
                    >= MAX ('Date'[Week] ) - 11
            )
                && (
                    'Date'[Week]
                        <= MAX ( 'Date'[Week] ) - 0
                )
        )
    ))

RETURN
DIVIDE(Total,Week,12)

View solution in original post

1 REPLY 1
Mahesh0016
Solution Sage
Solution Sage

_12wk Average =
VAR Week = CALCULATE(DISTINCTCOUNT('Date'[Week]),FILTER(
        ALL('Date'),
        (
            (
                'Date'[Week]
                    >= MAX ('Date'[Week] ) - 11
            )
                && (
                    'Date'[Week]
                        <= MAX ( 'Date'[Week] ) - 0
                )
        )
    ))
VAR Total=CALCULATE(SUM('Table'[Sales]),
    FILTER(
        ALL('Date'),
        (
            (
                'Date'[Week]
                    >= MAX ('Date'[Week] ) - 11
            )
                && (
                    'Date'[Week]
                        <= MAX ( 'Date'[Week] ) - 0
                )
        )
    ))

RETURN
DIVIDE(Total,Week,12)

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors