topic Re: Rolling Median Time Period in DAX Commands and Tips

Rolling Median Time Period

scaballerom — Thu, 15 Dec 2022 17:07:08 GMT

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

Re: Rolling Median Time Period

v-jialluo-msft — Fri, 16 Dec 2022 08:55:54 GMT

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.

Re: Rolling Median Time Period

scaballerom — Fri, 16 Dec 2022 11:52:10 GMT

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