DAX Rolling Median of a Measure by Time of Day

[jsflint]

I'm trying to come up with a DAX measure that calculates a rolling median based on time of day (minute by minute).  I would so appreciate anyone's help.  Below is a sample table with random numbers. 

Time	Current Minute Value	Previous Minute Value	Chg Pct	ABS Median Chg Pct
12:00:00 AM	0.785711
12:01:00 AM	0.26775	0.785711	65.92%
12:02:00 AM	0.747933	0.26775	179.34%
12:03:00 AM	0.526295	0.747933	29.63%
12:04:00 AM	0.78565	0.526295	49.28%
12:05:00 AM	0.457754	0.78565	41.74%
12:06:00 AM	0.3022	0.457754	33.98%
12:07:00 AM	0.591653	0.3022	95.78%
12:08:00 AM	0.473531	0.591653	19.96%
12:09:00 AM	0.167056	0.473531	64.72%
12:10:00 AM	0.165168	0.167056	1.13%
12:11:00 AM	0.443298	0.165168	168.39%
12:12:00 AM	0.429817	0.443298	3.04%
12:13:00 AM	0.754753	0.429817	75.60%
12:14:00 AM	0.405229	0.754753	46.31%
12:15:00 AM	0.565165	0.405229	39.47%	46.31%
12:16:00 AM	0.670337	0.565165	18.61%	41.74%
12:17:00 AM	0.274024	0.670337	59.12%	41.74%

First of all, I have a "Date" and "Day of Week" column that I purposely haven't added because I want to be able to add a filter visual to change to a different day of the week at will.  The time and value (current minute) columns are from the source table. The "previous minute value" is a calculated column that uses an index column with a lookup formula to display the previous minute value in the same row as the current minute value.  The "Chg Pct" is a measure that calculates the change in value from one minute to the next, and displays as an absolute number.  Here's the DAX measure:

CurPre =

var cur =

CALCULATE(

SUM(table[Current Minute Value]),

KEEPFILTERS(table)

)

var pre =

CALCULATE(

SUM(table[Prev Row]),

KEEPFILTERS(table)

)

return

ABS(DIVIDE(cur-pre,pre))

For one thing, the percentage change I get in DAX is different from the results I get in an Excel table.  I don't know if this DAX formula is set up right for this column anyway.

The last column in the table above are the results of a rolling median range.  "46.31%" is the median value for minutes from 12:15:00 AM back to 12:01:00 AM.  

The main issue is that I can't figure out how to create a minute-by-minute, rolling median column based on the "CurPre" measure in DAX.  I need to be able to calculate a rolling median of a measure that calculates minute-to-minute change percentage (15 total minutes at a time), and at the same time be able to select a different day of the week, and the times/values in the table switches to calculate the rolling median for that day of the week, and that time increment.  

Thank you for any advice you can provide!

[v-stephen-msft]

Hi @jsflint ,

I got the Previous Minute Value column and Chg Pct ABS column by creating calculated columns.

First, I add an index column in Power Query.

Then

Previous Minute Value = CALCULATE(SUM('Table'[Current Minute Value]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1))

Chg PctABS = ABS(DIVIDE(([Current Minute Value]-[Previous Minute Value]),[Previous Minute Value]))

The median of Chg Pct ABS, just use the MEDIAN function. Here I created the measure with MEDIAN.

Best Regards,

Stephen Tao

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

[jsflint]

Thanks, @v-stephen-msft .  It looks like your way (which is still great) comes to the same result as mine.  My goal is to generate a rolling median similar to a rolling average, and in a table and/or chart.

I do appreciate the application of EARLIER, though.  I've been trying to understand that particular function, and you've made it clear to me.

Do you know how I could add a column that will calculate a rolling median?  Thanks again!

[v-stephen-msft · Accepted solution]

Hi @jsflint ,

Sorry for not seeing your request clearly, this is a calculated column for rolling median.

Rolling Median =
IF (
    [Time] >= TIME ( 0, 15, 0 ),
    CALCULATE (
        MEDIAN ( 'Table'[Chg PctABS] ),
        FILTER (
            ALL ( 'Table' ),
            [Time]
                >= EARLIER ( 'Table'[Time] ) - TIME ( 0, 15, 0 )
                && [Time] <= EARLIER ( 'Table'[Time] )
        )
    )
)

Best Regards,

Stephen Tao

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

[jsflint]

Thanks!