cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Average Volume Week YTD by Store

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.

1 ACCEPTED SOLUTION
Community Support

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:

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.

2 REPLIES 2
Community Support

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:

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.

Solution Sage

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]

)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors