Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Trend of average # days per month

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!

 

 

1 REPLY 1
Anonymous
Not applicable

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors