Fill in blank values in time series data, based on last value.

[warrencowan]

Hi everyone

I am trying to create time series visuals (matrix and line charts primarly) from a snapshot table. Product have a position for the day, but different products in the table have different frequency schedules and days on which their position is recorded. I've pasted an example below. 

snapshot table

I have linked this table to a seperate date table in the model, but if I try to put this data in a matrix it highlights the blank values on the dates where there is no data. In line graphs this will produce very peaky and troughy lines for the blank date too.

snapshot table in matrix

I want to create a formula that populates each blank date cell with the value of the position from the most recent snapshot, and ideally looks further back than the last series of the dates that are filtered into the visual. I've illustrated below. 

matrix populated with formula field

The black dates in the above table are the ones from the snapshot table where as the orange ones are the ones I am trying to create from looking back for the last data where a vlaue is recorded. The green numbers are doing the same but I have highlghted them another colour to highlight that they are looking further back than the dates in the current table.

I am presuming I will need to create an alternative measure using look back functions like lastdate, last/first nonblank, or something, and chart this measure instead. But after days of mistakes I am struggling.

Any help warmly appreciated.

Warren

[warrencowan]

Thankyou both for coming to my aid, and thanks Imke for your solution. You have solved one of my oldest gripes with our data storeage. Previously we would have copied values across each day resulting in huge storage over time, and long data returns in queries. Many many thanks for your help.

[dkay84_PowerBI]

I think I've solved this (using query editor) but I'm not sure since your example data above is incomplete relative to the example of what you want for the finished product.  For example, in the finished matrix, for the first row (Product 1), how did you get all of those values based on the example you gave?  I assume that was based off of more complete data.

If this picture indicates that I have solved this, I will provide a link to the PBIX

[dkay84_PowerBI]

I can confirm that @ImkeF's solution works, and is much simpler and more performant than mine.  I need to improve my DAX skills...

[ImkeF · Accepted solution]

Hi Warren,

you need to connect your snapshot table to a date table. Then you can use a measure like this:

Last Position= 
CALCULATE( MAX( SnapshotTable[Position] )
            ,LASTNONBLANK( DATESBETWEEN( 'Date'[Date],BLANK(),LASTDATE( 'Date'[Date] ) )
                           ,CALCULATE( COUNT( SnapshotTable[Position] ))
                          )
          )

[Anonymous]

Great DAX, thanks! Follow-up question for you: how do you calculate Number of Product by Position for every date?