Measure to calculate ratio of current value with previous hour values

[pbidemouser2]

Hi guys,

I have a live connection dataset and I need to create a small measure to calculate ratio's. 

The dataset is as below, It is a 15 min interval dataset

Timestamp               Value

08-08-2020 2:00        34

08-08-2020 2:15        12

08-08-2020 2:30        15

08-08-2020 2:45          3

08-08-2020 3:00         3

08-08-2020 3:15         2

08-08-2020 3:30          11

I need to calculate the ratio of current value with the first 3 values of previous hour timestamps. For ex, if I am at 3: 15, it should take the avg of first 3 values of previous hour (2:00, 2:15, 2:30). avg=(34+12+15)/3

So the result should be (2/avg)

Similarly if the 3:30 timestamp comes in, the result should be 3/avg

Thanks

@amitchandak @parry2k 

[Greg_Deckler]

@pbidemouser2 - If you are trying to compare rows, see my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Vera_33]

Hi @pbidemouser2 

When 3:30 came in, should be 11/avg, right? Your dataset always has  :00, :15, :30, :45, total 4 values? And you have more than 1 day values? How to deal with no previous timestamp? I did a simple one based on your sample, as yours is live connection, so only can go with measures:

avg =

VAR CurTime = HOUR(SELECTEDVALUE( Table1[Timestamp]))

VAR T1 = FILTER(ALL(Table1), HOUR(Table1[Timestamp])<CurTime)

VAR T2 = ADDCOLUMNS(GROUPBY(T1,Table1[Timestamp]),"SUM", SUMX(TOPN(3,T1,[Timestamp],ASC),[Value]))

RETURN

MAXX(T2,[SUM])

ratio = DIVIDE( SELECTEDVALUE(Table1[Value]),[avg],"N/A")

[pbidemouser2]

Hi, @Vera_33 

I'm not sure how you calculated this, but the ratio seems wrong. 

The ratio at 3:00 should be = 3 / (avg of 34,12,15)

The ratio at 3:15 should be = 2 / (avg of 34,12,15)

[Vera_33 · Accepted solution]

Hi @pbidemouser2 

I calculated the sum of previous 3 values, so you want to average it like this? You did not say if you have more than 1 day or if your data is well structured, the measure needs to adjust accordingly.

avg =

VAR CurTime = HOUR(SELECTEDVALUE( Table1[Timestamp]))

VAR T1 = FILTER(ALL(Table1), HOUR(Table1[Timestamp])=CurTime-1)

VAR T2 = ADDCOLUMNS(GROUPBY(T1,Table1[Timestamp]),"SUM", SUMX(TOPN(3,T1,[Timestamp],ASC),[Value])/3)

RETURN

MAXX(T2,[SUM])

[simrantuli]

Hi @pbidemouser2,

What is '2/avg' and '3/avg' here?

Best Regards

Simran Tuli

[Fowmy]

@pbidemouser2 

I need a clarification:

If your current value is 3:15 then you need to average 2:15, 2:30 & 2:45 values then divide it by 3:15 value right?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[pbidemouser2]

If the current value is at 3:15, then i need to take the avg of 2:00, 2:15, 2:30.Lets call it as 'prev_avg'. Final value should be current value at 3:15 divided by prev_avg.

Similarly for 3:30, prev_avg will be the same but result is current value of 3:30 divided by prev_avg

@Fowmy 

[pbidemouser2]

Yes, correct @Fowmy 

[Fowmy]

@pbidemouser2 

Try this Measure: I can add validation in the measure to show blank for the first few rows if you need. 

Average First 3 PRev Hour = 
    
 var cd = SELECTEDVALUE(TIMEDATA[TimeStamp]) 
 var ch = CONVERT(INT(cd) +  (HOUR(cd)-1)/24,DATETIME)
 var t =  
SUMX(
        TOPN(3,
            FILTER(
                ALL(TIMEDATA),
                TIMEDATA[TimeStamp] >= ch),
        [TimeStamp],ASC,TIMEDATA[TimeStamp]),
        [Value]
    )
return


DIVIDE(
    SELECTEDVALUE(TIMEDATA[Value]),
    t
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Fowmy]

@pbidemouser2 

In that case, @Vera_33's solution should work for you. 

Let us know if you have difficulty implementing it.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn