Averaging COUNTROWS per week

DAX DAX Calculations
1

I feel like this should be simple but I’m not having much luck. I have a requirement to count the rows in a table then average that result per week. Just not completely sure of the syntax. I have a date table with week numbers. I’ve tried using SUMMARIZE to build a basic table first but then can’t work out how to average per week. Thank in advance for any assistance!

Cheers

EVALUATE
SUMMARIZE (
FILTER ( dimDates, dimDates[YearOffset] = 0 ),
dimDates[Week of Year],
dimDates[Year],
“Lines Per Week”, COUNTROWS ( ‘factSales Shipment Line’ )
)

2

@mkaess ,

Give this a go:

EVALUATE

VAR CurrentYrRows =
    CALCULATE(
        COUNTROWS( 'Sales'),
        'Dates'[Year] = YEAR( TODAY() )
        
    )

VAR ActiveWeeks =
    CALCULATETABLE(
        VALUES( 'Dates'[ISO Weeknumber] ),
        'Dates'[Year] = YEAR( TODAY() )
            && 'Dates'[IsAfterToday] = FALSE
    )
VAR vTable =
    ADDCOLUMNS(
        SUMMARIZE(
            FILTER(
                'Dates',
                'Dates'[Year] = YEAR( TODAY() )
                    && 'Dates'[ISO Weeknumber] IN ActiveWeeks
            ),
            'Dates'[ISO Weeknumber],
            'Dates'[Year]
        ),
        "@Lines Per Week", DIVIDE( CurrentYrRows, COUNTROWS( ActiveWeeks ) )
    )
RETURN
    vTable

Note: assumes you are using @Melissa 's Extended Date Table, to take advantage of the ISAFTERTODAY field.

I hope this is helpful.

  • Brian
3

Hi @mkaess, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @mkaess, we’ve noticed that no response has been received from you since July 6th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

I haven’t had the opportunity to look at this but will soon. Other priorities have come up. :slight_smile:

6

Hello @mkaess, following up if you were able to test the solution?

7

Hello @Mkaess, due to inactivity we’ll be tagging this post as solved.