Average Volume Week YTD by Store

[kbarc24]

Hi there, 

I'm stuck trying to come up with a way to calculate the following:
"The average weekly volume (count of rows) since the beginning of the year (YTD) OR since the first date with rows broken down by store."

That's a bit wordy so let me include a quick table. 

Week	1	2	3	4	5	6
Store A	10	16	17	20	18	23
Store B			5	7	9	15
Store C					9	10

Ideally what we'd see is that for Store A, at week 6, our measure would produce ((10+16+17+20+18+23)/6) = 17.3. For Store B; 9. And Store C: 9.5

In a perfect world that average would be rolling, so I could look at week 5 and get 16.2, 7, and 9 respectively. 

I've been able to make it work for cases where there is data going back right to the start of the year, but I can't figure out a way to write the calculation that specifies an average only if the weeks have data.

[Anonymous · Accepted solution]

Hi, @kbarc24 

Based on your information, I create a sample table:

Then create measures and try the following DAX expression:

Rolling Average Volume A = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store A] <> BLANK()
    ),
    'Table'[Store A]
)

Rolling Average Volume B = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store B] <> BLANK()
    ),
    'Table'[Store B]
)

Rolling Average Volume C = 
VAR CurrentWeek = MAX('Table'[Week])
RETURN
AVERAGEX(
    FILTER(
        ALL('Table'),
        'Table'[Week] <= CurrentWeek && 'Table'[Store C] <> BLANK()
    ),
    'Table'[Store C]
)

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[Shravan133]

To calculate the average weekly volume (count of rows) since the beginning of the year (YTD) or since the first date with rows broken down by store, you can create a measure in DAX that takes into account only the weeks where there is data.

1. Create a Measure for Cumulative Sum of Rows

First, create a measure to calculate the cumulative sum of rows for each store up to the current week.

Cumulative Rows =

CALCULATE(

    SUM('Table'[Rows]),

    FILTER(

        ALL('Table'),

        'Table'[Store] = MAX('Table'[Store]) &&

        'Table'[Week] <= MAX('Table'[Week])

    )

)

1. Create a Measure for the Count of Weeks with Data

Next, create a measure to count the number of weeks that have data for each store up to the current week.

Weeks with Data =

CALCULATE(

    COUNTROWS('Table'),

    FILTER(

        ALL('Table'),

        'Table'[Store] = MAX('Table'[Store]) &&

        'Table'[Week] <= MAX('Table'[Week]) &&

        'Table'[Rows] > 0

    )

)

1. Create the Rolling Average Measure

Finally, create the rolling average measure by dividing the cumulative sum by the count of weeks with data.

Rolling Average =

DIVIDE(

    [Cumulative Rows],

    [Weeks with Data]

)