Average the count of strings in my table over a period of time for a line graph

[pmh_001]

Hi, I have a table with two columns. One column is a DateTime column that shows the date and time that an item was moved. The other column is the ItemNumber of the item moved. Each row includes only one item number - as items are moved one at a time. There are multiple rows per day - and sometimes the days have gaps between them. The data stretches through 10 years. Here is a sample table:

DateTime                        Item Number

05/25/2021 10:38:07 A.M	SGH123
05/25/2021 11:32:42 A.M	APJ432
05/26/2021 11:17:21 P.M	SGH123
05/26/2021 03:12:01 P.M	SGH123
05/29/2021 09:22:49 A.M	JRG872

How can I average the transaction counts by year to display a trend on a line graph? Also, can I set my graph to only show the average trend for one specific item if that item is filtered?

Thank you so much in advance! I have been struggling greatly with how to do this.

[Nathaniel_C · Accepted solution]

Hi @pmh_001 ,
Try this:

Count per year =
VAR _itemNumber =
    MAX ( myTable[ItemNumber] ) // get the current item number of the row we are on
VAR _year =
    YEAR ( MAX ( myTable[DateTime] ) ) // get the current year of the row we are on
VAR _count =
    CALCULATE (
        COUNTROWS ( myTable ),
        FILTER (
            ALLEXCEPT ( myTable, myTable[ItemNumber] ),
            MAX ( myTable[ItemNumber] ) = _itemNumber
                && YEAR ( myTable[DateTime] ) = _year
        )
    ) //filter the table to only include those rows that match the year and item number, and count how many rows
RETURN
    _count

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[pmh_001]

This worked. Thank you so very much!

[Nathaniel_C]

You are welcome!

[aj1973]

Hi @pmh_001 

The best practise is to first split the column DateTime into to columns Date and Time. Then add a calendar table to your model and link it(1 to many) to the Date column from your table. Use calendar date in your graph and add a slicer for Items in your page.