locked
Average Items Per Day RRS feed

  • Question

  • I know this will be simple, but I can't seem to figure it out..

    I have two tables.  Items and Date

    Items looks like this:

    ItemNum - Category - Owner - ItemDate
    123 - Yellow - Fred - 1/1/2013
    234 - Red - Susy - 3/5/2014
    345 - Blue - Larry - 1/3/2015
    456 - Green - Bob - 1/4/2015


    The Items table actually have 250,000 rows spanning 2014 to today.  The date table is a typical date dimension from 2010 to 2020.

    I want to create a measure that's the 'Average Item Per Day' - and I expect it to change as I filter the Item table and select a date range using PowerView/Excel date slicers.

    I don't think I want a moving average, I simply want the average daily item per day.  So, if I filter for 2015 and it's Jan 23rd (today), then I would expect this equation using the example above 2/23 = .086 Items Per Day.  This is because there are only two items for 2015 and so far, there are 23 days in 2015.

    If I choose 2014, I would expect 1/365 or .00273. 

    I've tried this:

    CALCULATE(AVERAGEX('Items',COUNTROWS('Items')),FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
        ) )


    Doesn't work.

    Please help.


    Saturday, January 24, 2015 3:37 AM

Answers

  • After a long weekend transfixed on this issue, i tried to use the pattern here: http://www.sqlbi.com/articles/rolling-12-months-average-in-dax/.

    Instead of [Sales] as this example illustrates, i used [Items] (which for me is just a countrows(items) measure).

    The biggest help here is the creation of the demoniator to be used in the final formula to average them.  You can create one of these for day as well by replacing [MonthName] with [Date].  That finally yielded the 23 days in Jan where in the past i kept getting 365 for 2015.

    All seems to work, but let me know if there was an easier way of doing it.

    Months12M:=CALCULATE (
        CALCULATE ( COUNTROWS ( VALUES ( Calendar[MonthName] ) ), Sales ),
        DATESBETWEEN (
            Calendar[FullDate],
            SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( Calendar[FullDate] ) ) ),
            LASTDATE ( Calendar[FullDate] )
        )
    )

     
    • Proposed as answer by Charlie Liao Tuesday, January 27, 2015 8:36 AM
    • Marked as answer by Eric Vogelpohl Tuesday, January 27, 2015 6:42 PM
    Sunday, January 25, 2015 10:49 PM
  • something like this should work:

    Avg Items Day:=DIVIDE(
    TOTALYTD(DISTINCTCOUNT('Internet Sales'[ProductKey]), 'Date'[Date]),
    COUNTROWS(DATESYTD('Date'[Date]))
    )

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Eric Vogelpohl Tuesday, January 27, 2015 6:42 PM
    Monday, January 26, 2015 8:55 AM
    Answerer

All replies

  • After a long weekend transfixed on this issue, i tried to use the pattern here: http://www.sqlbi.com/articles/rolling-12-months-average-in-dax/.

    Instead of [Sales] as this example illustrates, i used [Items] (which for me is just a countrows(items) measure).

    The biggest help here is the creation of the demoniator to be used in the final formula to average them.  You can create one of these for day as well by replacing [MonthName] with [Date].  That finally yielded the 23 days in Jan where in the past i kept getting 365 for 2015.

    All seems to work, but let me know if there was an easier way of doing it.

    Months12M:=CALCULATE (
        CALCULATE ( COUNTROWS ( VALUES ( Calendar[MonthName] ) ), Sales ),
        DATESBETWEEN (
            Calendar[FullDate],
            SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( Calendar[FullDate] ) ) ),
            LASTDATE ( Calendar[FullDate] )
        )
    )

     
    • Proposed as answer by Charlie Liao Tuesday, January 27, 2015 8:36 AM
    • Marked as answer by Eric Vogelpohl Tuesday, January 27, 2015 6:42 PM
    Sunday, January 25, 2015 10:49 PM
  • something like this should work:

    Avg Items Day:=DIVIDE(
    TOTALYTD(DISTINCTCOUNT('Internet Sales'[ProductKey]), 'Date'[Date]),
    COUNTROWS(DATESYTD('Date'[Date]))
    )

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by Eric Vogelpohl Tuesday, January 27, 2015 6:42 PM
    Monday, January 26, 2015 8:55 AM
    Answerer
  • Thanks Gerhard.  Worked, though a curios thing happens for dates past today, if i filter just on 2015.  The graph continues out to the end of 2015, with a trend downward slope per day.  I actually wanted it to stop at today.

    No need to continue.  The pattern i shared above actually worked better than I originally stated.  A rolling 12 months is actually a better way of seeing this data, versus avg/day.

    -Eric.

    Tuesday, January 27, 2015 6:42 PM