Data Smoothing for missing DateTime bands

[BertieGG]

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 using DAX which contains these missing datapoints, but I'm struggling to write the DAX that will successfully smooth the line (basically average it out across all the missing datapoints) and not show such a large outlier?

[selimovd]

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]

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

Reading_Date	Total	Delta
29/04/2022 06:30	1476	0
29/04/2022 07:00	1479	3
29/04/2022 07:30	1480	1
29/04/2022 08:00	1482	2
29/04/2022 08:30	1483	1
29/04/2022 09:00	1485	2
29/04/2022 09:30	1488	3
29/04/2022 10:00	1495	7
29/04/2022 10:30	1496	1
29/04/2022 11:00	1498	2
02/05/2022 20:00	1700	202
02/05/2022 20:30	1701	1
02/05/2022 21:00	1703	2
02/05/2022 21:30	1706	3
02/05/2022 22:00	1707	1
02/05/2022 22:30	1708	1
02/05/2022 23:00	1710	2
02/05/2022 23:30	1715	5
03/05/2022 00:00	1716	1

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