Last moving 3 month Average

[scaballerom]

Hi all,

I have the following data set sample:

job_name	delivery_starting_date	delivery_starting_datetime	delivery_ending_datetime	JobDurationYesterday
A	07/12/2022	07/12/2022 1:00	07/12/2022 2:30	1,5
B	07/12/2022	07/12/2022 7:00	07/12/2022 7:30	0,5
C	07/12/2022	07/12/2022 6:00	07/12/2022 7:00	1
A	06/12/2022	06/12/2022 0:00	06/12/2022 1:00	1,0
B	06/12/2022	06/12/2022 7:00	06/12/2022 9:00	2,0
C	06/12/2022	06/12/2022 7:00	06/12/2022 10:00	3
A	05/12/2022	05/12/2022 8:00	05/12/2022 10:00	2

In my current page, what I want to see is all the deliveries that started after yesterday's date at 18:00:00, for which I have a Dim_Table like the following table. It is related with the first table by delivery_starting_date and Date:

Date	Yesterday
05/12/2022	TODAY()-1 18:00:00
06/12/2022	TODAY()-1 18:00:00
07/12/2022	TODAY()-1 18:00:00
08/12/2022	TODAY()-1 18:00:00
09/12/2022	TODAY()-1 18:00:00
10/12/2022	TODAY()-1 18:00:00
11/12/2022	TODAY()-1 18:00:00

What I would like to obtain is the Average Job Duration of the Last Three months (from date: yesterday at 18:00:00 minus three months) by job name, so I obtain the following table:

job_name	delivery_starting_date	delivery_starting_datetime	JobDurationYesterday	Avg Job Duration Last Three Months
A	07/12/2022	07/12/2022 1:00	1,5	1,5
B	07/12/2022	07/12/2022 7:00	0,5	1,3
C	07/12/2022	07/12/2022 6:00	1	2

Could someone help me out, please?

Thanks in advance for your help!

BR,

Sara

[amitchandak · Accepted solution]

@scaballerom , with a date not datetime joined with date table

Monthly avg max date -1

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]) -1 ,-3,MONTH))

daily Avg

Monthly avg max date -1

Rolling 3 = calculate(AverageX(Values('Date'[Date]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]) -1 ,-3,MONTH))

Monthly avg max yesterday

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],today() -1 ,-3,MONTH))

In case you need it between dates

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

[amitchandak · Accepted solution]

@scaballerom , with a date not datetime joined with date table

Monthly avg max date -1

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]) -1 ,-3,MONTH))

daily Avg

Monthly avg max date -1

Rolling 3 = calculate(AverageX(Values('Date'[Date]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]) -1 ,-3,MONTH))

Monthly avg max yesterday

Rolling 3 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],today() -1 ,-3,MONTH))

In case you need it between dates

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

[scaballerom · Accepted solution]

Hi @amitchandak ,

Thanks for the tips; however, they did not work for me; at the end, what worked was:

50PctlDurationL3M =

VAR NumberOfDays = 91

VAR MaxDay = SELECTEDVALUE('Fact Jobs'[job_starting_date])

VAR MinDay = MaxDay-NumberOfDays

VAR Result =

        CALCULATE(PERCENTILE.EXC('Fact Jobs'[job_elapsed_adj],0.5),'Fact Jobs'[job_status]="Completed",ALL('Fact Jobs'),'Fact Jobs'[job_key]=SELECTEDVALUE('Fact Jobs'[job_key]),'Fact Jobs'[job_starting_date]<=MaxDay,'Fact Jobs'[job_starting_date]>MinDay)

RETURN

Result