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 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?

Need Help

2 Replies

  • 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