Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
scaballerom
Helper I
Helper I

Last moving 3 month Average

Hi all,

 

I have the following data set sample:

job_namedelivery_starting_datedelivery_starting_datetimedelivery_ending_datetimeJobDurationYesterday
A07/12/202207/12/2022 1:0007/12/2022 2:301,5
B07/12/202207/12/2022 7:0007/12/2022 7:300,5
C07/12/202207/12/2022 6:0007/12/2022 7:001
     
A06/12/202206/12/2022 0:0006/12/2022 1:001,0
B06/12/202206/12/2022 7:0006/12/2022 9:002,0
C06/12/202206/12/2022 7:0006/12/2022 10:003
     
A05/12/202205/12/2022 8:0005/12/2022 10:002

 

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:

DateYesterday
05/12/2022TODAY()-1 18:00:00
06/12/2022TODAY()-1 18:00:00
07/12/2022TODAY()-1 18:00:00
08/12/2022TODAY()-1 18:00:00
09/12/2022TODAY()-1 18:00:00
10/12/2022TODAY()-1 18:00:00
11/12/2022TODAY()-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_namedelivery_starting_datedelivery_starting_datetimeJobDurationYesterdayAvg Job Duration Last Three Months
A07/12/202207/12/2022 1:001,51,5
B07/12/202207/12/2022 7:000,51,3
C07/12/202207/12/2022 6:0012

 

scaballerom_0-1670433649336.png

 

Could someone help me out, please?


Thanks in advance for your help!


BR,

Sara

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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

View solution in original post

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

View solution in original post

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors