Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I just subscribed to this community, looks awesome. I've got a question, hopefully someone can help me.
I've got the following dataset (it's a long list with a lot of documents). I've created the column booked -/- received with datediff.
Date document received Date document booked Booked -/- received
3/4/2018 07:47 5/4/2018 13:15 2 (days), rounded
5/4/2018 08:15 8/4/2018 15:15 3 (days), rounded
etc. etc. etc.
The question:
I would like to measure the average number of days (booked -/- received) of all documents per month. So e.g. in january the average was 2,3 days, in february 2,8 days, etc. This data should be presented in a line graph. So I want to have insight in the trend of booked -/- received.
What's the most easy way to get there? I am trying to use the calculate-function (in measure column), but I am not succesfull yet.
Can someone help me on this?
Many thanks!
Power BI can do this natively for you.
First, create a new Date Column in your data table, which uses the First of the month based on the date column of whichever data field you want to group by. I.e. which month would an item fall into if its Booked in November but the document was received in December?
Place the Line Graph visualisation onto your Report Canvas. Put that first of the month column into the Axis. You can use the format area to display it as "Month Year" rather than "1/MM/YYYY" or the like.
Now put your Booked -/- Received column into the Values section of your graph. I'm assuming here that your Booked -/- Received column is actually numbers, not a text string that you've written. Once you've put that into your Values box, there is a little drop arrow under where you placed the field. Just selected "Average".
Now you'll have a line graph, with the average number per month.
User | Count |
---|---|
64 | |
27 | |
25 | |
17 | |
11 |