Help creating value by time to smooth data

[dmoggie]

Hi All,

I have been task with smoothing some data using the following simple calculation

85% of previous minute value + 15% of current minute value = 

Data is recorded in minute intervals for 24hrs a day in one column

what would be the best approach?

Any help would be greatly appreciated.

KR

[Anonymous]

@dmoggie 

Try first create a time column:

Time = TIME(HOUR([Datetime]),MINUTE([Datetime]),SECOND([Datetime]))

Then you may create the formula based on the minutes.

Measure = 
var This Min = CALCULATE(sum('Table'[Value]), FILTER(ALL('Table'),'Table'[time]=max('Table'[time]))
var Prev Min = CALCULATE(sum('Table'[Value]),FILTER(ALL('Table'),'Table'[time]=max('Table'[time])-"00:01:00"))

Return [Prev Min] *.85 + [This Min] *.15

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dmoggie]

Hi I just cant seems to get this working.  example of data table I am wokring with. You can see I already have seperate Date and Time columns. 

Device Address	Date	Time	Flow
346	28/09/2020	00:00	2
346	28/09/2020	00:01	1
346	28/09/2020	00:02	1
346	28/09/2020	00:03	0
346	28/09/2020	00:04	5
346	28/09/2020	00:05	0
346	28/09/2020	00:06	0
346	28/09/2020	00:07	1
346	28/09/2020	00:08	0
346	28/09/2020	00:09	0
346	28/09/2020	00:10	1

Really stuck here 😞

[dmoggie]

Bumping thread. Anyone?

[amitchandak]

@dmoggie , Join date of table with date of date table .

It is datetime then create date without time

new column

date = [datetime].date

measure

This Day = CALCULATE(Min('Table'[Value]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE('Table'[Value]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE('Table'[Value]), previousday('Date'[Date]))

final Measure = [Last Day] *.85 + [This Day] *.15

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[dmoggie]

Hi @amitchandak thank you for your quick response.  Re-reading my post I dont think i was clear. I need to work on values each minute of the day.