cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Last moving 3 month Average

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?

BR,

Sara

2 ACCEPTED SOLUTIONS
Super User

@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

Helper I

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
2 REPLIES 2
Super User

@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

Helper I

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors