Forum Discussion

scaballerom's avatar
scaballerom
Helper I
3 years ago
Solved

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

 

 

Could someone help me out, please?


Thanks in advance for your help!


BR,

Sara

 

DAX
MovingAverage
  • amitchandak's avatar
    amitchandak
    3 years ago

    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's avatar
    scaballerom
    3 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      scaballerom
      Helper I
      3 years ago

      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