I have numeric data collected from sensors to record silt levels/qty. The sensor should record the level at least every day but sometimes there is an outtage and some dates have no data.
What I would like to do is to pull in the silt level for the last recorded date and use this as the quantity for the dates with the “missing” data. i.e. if a Date[Date] slicer is used there can be no selection that shows a blank visual.
I have tried to solve this using LASTNONBLANK and the Prior Date Variable DAX from the video called “Compare One Sales Versus the Last Sale” as I thought these may help - I think I am close but I can’t work out how to get the result of backfilling that I want.
Hopefully you can see the snip of my results;
Last Silt Reading = CALCULATE([Silt Level], LASTNONBLANK('Date'[Date], [Silt Level]))
Prior Silt Reading =
VAR PriorSiltReading = CALCULATE(MAX(Gully[Received at New]),
FILTER(ALL(Gully[Received at New]),Gully[Received at New] < MAX(Gully[Received at New])))
RETURN
CALCULATE([Silt Level],
FILTER(ALL(Gully[Received at New]),
Gully[Received at New] = PriorSiltReading))
I hadn’t thought of fixing this at the query editor stage. I have used fill down and fill up before but I don’t know how to create a column to mark the empty values. In my data for example there is only 2 rows 13-Feb-19 and 21-Feb-19 so I need to insert new rows, one for each of 14th, 15th etc etc.
I should probably mention that I kept my sample data simple but in the full table there are multiple rows for the same date as data readings are taken every hour, but when the sensor fails a whole day is missing.
I only need one row adding for each missing date but I thought it useful to know that the table structure in not one row per date when the records exist, thanks
Suggest to merge a duplicate of the date column in your datetable, with your data, so you will have a consistent range of dates. The blanks in your data can subsequently be completed with fill.
Just in case you would like to mark each record with actual or previous value, so you can measure the occurrence of blank values, a Conditional Column will do the trick.
Is there a chance you can upload some of the sample data? As @Paul mentioned, this can be done in Power Query, but there can be more than a few variables to account for.
