Cumulative average per week

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways to achieve this is to use WINDOW DAX function in the measure.

 

 

 

 

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

 

 

expected result: =
VAR _t =
    ADDCOLUMNS (
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
            ORDERBY ( 'calendar'[Year-Month sort], ASC )
        ),
        "#avgsalespermonth", [average sales per month:]
    )
VAR _countrows =
    COUNTROWS ( _t )
RETURN
    IF (
        NOT ISBLANK ( [average sales per month:] ),
        SUMX ( _t, [#avgsalespermonth] ) / _countrows
    )

 

 

Hi Syndicate_Admin ,

I very much recognize Jihwan_Kim's idea, but I created a form just the same.

So I think you can create a calculated column and here is the DAX code.

Column = 
VAR CurrentRow = 'Table'[Number]
RETURN
    DIVIDE(
        SUMX(
            FILTER(
                'Table',
               'Table'[Number] <= CurrentRow
            ),
            'Table'[Value]
        ),
        CurrentRow
    )

 

 

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.