Forum Discussion

BertieGG's avatar
BertieGG
Frequent Visitor
4 years ago

Data Smoothing for missing DateTime bands

Hi folks, I have a scenario where I am missing some data between two datetime points which is then creating a large datapoint for the next delta.       I have created a table usi...
Need Help
selimovd's avatar
selimovd
Most Valuable Professional
4 years ago

Hey BertieGG ,

 

actualy you have to define the criteria.

My approach would be to ignore all delta-values that are bigger than 10 x the average of all delta.

 

The following measure should do that:

Delta without Peaks = 
VAR vTotalAverageDelta = CALCULATE ( AVERAGE ( myTable[Delta] ), ALL ( myTable ) )
VAR vResult =
    AVERAGEX (
        myTable,
        IF ( vTotalAverageDelta * 10 < myTable[Delta], BLANK (), myTable[Delta] )
    )
RETURN
    vResult

 

This is how the result would look like (first with the normal column and second visual with the measure):

I also attached my demo file.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

 

Also a tipp for next time, please provide the data as a table or something I can copy and put in a new Power BI file. Like that I have to create a table and enter all data manually what takes a lot of time....


Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

BertieGG's avatar
BertieGG
Frequent Visitor
4 years ago

Thanks for your response.
Good point regarding the supply of the dataset.  I have copied and pasted a sample below

Reading_DateTotalDelta
29/04/2022 06:3014760
29/04/2022 07:0014793
29/04/2022 07:3014801
29/04/2022 08:0014822
29/04/2022 08:3014831
29/04/2022 09:0014852
29/04/2022 09:3014883
29/04/2022 10:0014957
29/04/2022 10:3014961
29/04/2022 11:0014982
02/05/2022 20:001700202
02/05/2022 20:3017011
02/05/2022 21:0017032
02/05/2022 21:3017063
02/05/2022 22:0017071
02/05/2022 22:3017081
02/05/2022 23:0017102
02/05/2022 23:3017155
03/05/2022 00:0017161

 

What I am attempting to do is show the increase in the Total by 30 minute intervals, so I actually have 48 datapoints per day.  
My Original message talks about "Missing Datapoints" that should have read "DateTimes".
(I don't seem to be able to insert links to my files due to organisational settings, and there is no abilty to attach files, so I created this in Excel and added 1/48 to the missing data point)

Thanks again for your assistance