Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
This worked. Thank you so very much!
You are welcome!
Proud to be a Super User!
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
User | Count |
---|---|
93 | |
83 | |
77 | |
71 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |