Average time taken in last 7/14/30/90/365 days

[pripatel2787]

Hi,

I am trying to calculate how long it has taken to approve/reject work orders in the last 7/14/30/90/365 days in power bi to show something like this:

e.g. in the last 7 days it has taken an everage of 3 days to approve/reject works relating to Autodoors.

The 7/14/30/90/365 dates would need to be based of the latest 'date created' in the data table

my data table has these headers:

is this possible?

Thanks

[pripatel2787]

Thanks - i have used dummy data - is this table ok?

Work Request Number	Asset Subcategory	WR Created Date	Days to Approve/Reject
214174	Electrical Installation Condition Report	10 Aug 23	182
217732	Electrical Installation Condition Report	3 Jul 23	1
226457	Autodoors	20 Apr 23	0
206910	L8 Water Check (M&E)	20 Dec 23	2
211657	L8 Water Check (M&E)	27 Sep 23	6
206126	Electrical Installation Condition Report	15 Jan 24	8
239639	Electrical Installation Condition Report	29 Nov 22	98
233239	Electrical Installation Condition Report	15 Mar 23	2
211864	Gas Safety Test	22 Sep 23	11
216448	Autodoors	17 Jul 23	3
238181	Autodoors	8 Feb 23	27
231165	Autodoors	28 Mar 23	7
237634	Autodoors	14 Feb 23	21
218670	Autodoors	28 Jun 23	1
235375	Mortuary Trolley	1 Mar 23	8
224794	Mortuary Trolley	1 May 23	3
224038	Hydraulic Mortuary Trolley	5 May 23	5
206563	Autodoors	2 Jan 24	21
199013	Autodoors	22 Feb 24	4
198068	Autodoors	26 Feb 24	1
229500	L8 Water Check (M&E)	5 Apr 23	29
227456	L8 Water Check (M&E)	17 Apr 23	17
211301	Electrical Installation Condition Report	4 Oct 23	9
210173	Electrical Installation Condition Report	31 Oct 23	3
207646	Autodoors	11 Dec 23	59
226641	L8 Water Check (M&E)	20 Apr 23	13
227153	L8 Water Check (M&E)	18 Apr 23	15
210903	Electrical Installation Condition Report	13 Oct 23	0
216063	Electrical Installation Condition Report	19 Jul 23	0
214532	Electrical Installation Condition Report	2 Aug 23	5
222217	Gas Safety Test	19 May 23	3
207901	Autodoors	4 Dec 23	0
199417	Autodoors	21 Feb 24	5
205656	Autodoors	24 Jan 24	5
206847	Autodoors	21 Dec 23	18
199617	Autodoors	19 Feb 24	7
238429	Mortuary Trolley	7 Feb 23	28
238622	Mortuary Trolley	3 Feb 23	32
210727	Electrical Installation Condition Report	16 Oct 23	18
213015	Electrical Installation Condition Report	4 Sep 23	126
213931	Electrical Installation Condition Report	15 Aug 23	70
212693	Electrical Installation Condition Report	7 Sep 23	29
207679	Autodoors	6 Dec 23	16
213030	Electrical Installation Condition Report	1 Sep 23	0
233292	Lifting Equipment and Fall Arrest Systems	15 Mar 23	2
235799	Lifting Equipment and Fall Arrest Systems	27 Feb 23	98
216265	Electrical Installation Condition Report	18 Jul 23	48
213066	Electrical Installation Condition Report	31 Aug 23	4
211642	Electrical Installation Condition Report	27 Sep 23	13
223660	Hydraulic Mortuary Trolley	9 May 23	1
207708	Autodoors	6 Dec 23	33
219976	Fire Alarm Systems	13 Jun 23	1
207624	Autodoors	11 Dec 23	28
240258	Refrigerant Gas Compliance	17 Nov 22	110
239111	Passenger Lift	25 Jan 23	41
232273	Lifting Equipment and Fall Arrest Systems	23 Mar 23	12

[lbendlin]

see attached

[lbendlin]

yes, that seems to be very doable. What have you tried and where are you stuck?

[pripatel2787]

Hi, thats great - I have started by creating measures for each of the 7/14/30/90/365 days:

SevenDayRollingAvg =

AVERAGEX(

    DATESINPERIOD(Closed[WR Created Date].[Date], LASTDATE(Closed[WR Created Date].[Date]), -7, DAY),

    CALCULATE(SUM(Closed[Days to Approve/Reject]))

)

but this just give me blanks

[lbendlin · Accepted solution]

You could do that, or you could have a reference table with the date ranges and then use a single measure.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[pripatel2787]

Thanks a lot! This is spot on! 👍