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;
The DAX I have tried =
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))