Forum Discussion

warrencowan's avatar
warrencowan
Icon for Kudo Collector rankKudo Collector
9 years ago
Solved

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 differ...
Need Help
Tutorial Requests
  • ImkeF's avatar
    ImkeF
    9 years ago

    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] ))
                          )
          )
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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