Forum Discussion

danielhough's avatar
danielhough
Helper II
3 years ago
Solved

Carry Forward Values until Change

Hello everyone! I have a issue below that I am trying to resolve. I have daily rates in the below table. The rate for July, 1.14043 remains constant throught its next change on August 1, 1.55169. I n...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    The method I posted was using measures.

    If you want it as a calculated column, use:

    Filled Value =
VAR _LNB =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && NOT ISBLANK ( 'Table'[Daily Rate] )
        )
    )
RETURN
    LOOKUPVALUE ( 'Table'[Daily Rate], 'Table'[Date], _LNB )

     

davehus's avatar
davehus
Memorable Member
3 years ago

Hi danielhough , Are you able to sort the table in powerquery and use the fill down function to fill the null values?

 

Did I help you today? Please accept my solution and hit the Kudos button.

danielhough's avatar
danielhough
Helper II
3 years ago

Hey! So this is a calulcated table so I don't see it in Power Query, but I essentially need to fill "up" the numbers from old to new. 

    • danielhough's avatar
      danielhough
      Helper II
      3 years ago

      Heres the code I used and the result, am I not placing the variables correctly?

       

      Column =
      VAR temp =
      TOPN (
      1,
      FILTER ( '3. RATES_TABLE_MMMF', [DAILY_RATE] = EARLIER ( [DAILY_RATE] ) && [Date] > EARLIER ( [Date] ) ),
      [Date], ASC
      )
      VAR result =
      MINX ( temp, [DAILY_RATE] )
      RETURN
      IF ( ISBLANK ( result ), [DAILY_RATE], result )