Rolling Median Time Period

[scaballerom]

Hi all,

I need to dynamically calculate the median of the last three months of the starting time of the distinct jobs of the current selected date, that is:

I want to calculate, for each distinct job key, the median of its "Start" time of the last three months.

I tried:

MedianStartingL3M =

VAR NumberOfDays = 91

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

VAR MinDay = MaxDay-NumberOfDays

VAR Result =

        CALCULATE(PERCENTILE.EXC('Fact Jobs'[job_start_time],0.5),ALL('Fact Jobs'),'Fact Jobs'[job_key]=SELECTEDVALUE('Fact Jobs'[job_key]),'Fact Jobs'[job_start_datetime]<=MaxDay,'Fact Jobs'[job_start_datetime]>MinDay)

RETURN

Result

Where

job_start_time = time when the job starts. In the first example: 14:28:49

job_key = unique identifier of the job

job_start_datetime = datetime when the job starts. In the first example 15/12/2022 14:28:49

But it's not working

Could someone help me out?

Thanks in advance for your help.

BR,

Sara

[v-jialluo-msft]

Hi  @scaballerom ,

You can try this measure:

MEASURE = 
CALCULATE (
    PERCENTILE.EXC ( 'Fact Jobs'[job_start_time], 0.5 ),
    FILTER (
        ALL ( 'Fact Jobs' ),
        'Fact Jobs'[job_key] = SELECTEDVALUE ( 'Fact Jobs'[job_key] )
            && 'Fact Jobs'[job_start_datetime] <= MaxDay
            && 'Fact Jobs'[job_start_datetime] > MinDay
    )
)

If that's not what you need, provide sample files and expected output.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[scaballerom]

Hi @v-jialluo-msft ,

I get the following error:

Please find bellow some sample data, as the expected results:

job_name	job_starting_time_adj	job_status	job_starting_datetime_adj
1	18:00:01	Completed	15/09/2022 18:00
1	18:00:09	Completed	18/09/2022 18:00		Median	18:00:09
1	18:00:01	Completed	19/09/2022 18:00		Average	18:00:08
1	18:00:01	Completed	20/09/2022 18:00
1	18:00:07	Completed	21/09/2022 18:00
1	18:00:09	Completed	22/09/2022 18:00
1	18:00:14	Completed	25/09/2022 18:00
1	18:00:12	Completed	26/09/2022 18:00
1	18:00:03	Completed	27/09/2022 18:00
1	18:00:13	Completed	28/09/2022 18:00
1	18:00:18	Completed	29/09/2022 18:00
1	18:00:07	Completed	02/10/2022 18:00
1	18:00:15	Completed	03/10/2022 18:00