Forum Discussion

kbarc24's avatar
kbarc24
Regular Visitor
2 years ago
Solved

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 br...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Shravan133
Super User
2 years ago

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]

)