cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
warrencowan Regular Visitor
Regular Visitor

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

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. 

 

example of date based data for powerbi subission 2017-01-08 18-18-34.pngsnapshot 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.

 

example of date based data for powerbi subission 2017-01-08 18-19-38 - 2.pngsnapshot 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. 

example of date based data for powerbi subission 2017-01-08 18-20-16 - 3.pngmatrix 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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] ))
                          )
          )

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




5 REPLIES 5
Super User
Super User

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

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] ))
                          )
          )

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




dkay84_PowerBI New Contributor
New Contributor

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

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

 

Capture.PNG

dkay84_PowerBI New Contributor
New Contributor

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

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

warrencowan Regular Visitor
Regular Visitor

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

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.

jdugre Frequent Visitor
Frequent Visitor

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

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