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 )

     

danielhough's avatar
danielhough
Helper II
3 years ago

This is where I'm at now with the latest DAX:

 

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

Try:

Filled Value =
VAR _LNB =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && 'Table'[Uinque key] = EARLIER ( 'Table'[Uinque key] )
                && NOT ISBLANK ( 'Table'[Daily Rate] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Daily Rate] ),
        FILTER (
            'Table',
            'Table'[Date] = _LNB
                && 'Table'[Uinque key] = EARLIER ( 'Table'[Uinque key] )
        )
    )
  • danielhough's avatar
    danielhough
    Helper II
    3 years ago

    THnaks for the help Paul, but I am having the same issue. I attached a file with both an excel and a PBIX for refrence

    DropBox 

     

     

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

      The error you're getting is because you need to write the proper field name as per your dataset. I believe it is '3. RATES_TABLE_MMMF'[DAILY-RATE]

       

      (instead of '3. RATES_TABLE_MMMF'[Daily Rate] which is what you now have in your code)

      • danielhough's avatar
        danielhough
        Helper II
        3 years ago

        You're Right, that did fix it, but for some reason its still not populating the blanks with 1.1404

         

         

         

        Heres the code:

         

        Filled Value = 
VAR _LNB =
    CALCULATE (
        MAX ( '3. RATES_TABLE_MMMF'[Date] ),
        FILTER (
            '3. RATES_TABLE_MMMF',
            '3. RATES_TABLE_MMMF'[Date] <= EARLIER ( '3. RATES_TABLE_MMMF'[Date] )
                && '3. RATES_TABLE_MMMF'[Unique key] = EARLIER ( '3. RATES_TABLE_MMMF'[Unique key] )
                && NOT ISBLANK ( '3. RATES_TABLE_MMMF'[Daily_Rate] )
        )
    )
RETURN
    CALCULATE (
        MAX ( '3. RATES_TABLE_MMMF'[Daily_Rate] ),
        FILTER (
            '3. RATES_TABLE_MMMF',
            '3. RATES_TABLE_MMMF'[Date] = _LNB
                && '3. RATES_TABLE_MMMF'[Unique key] = EARLIER ( '3. RATES_TABLE_MMMF'[Unique key] )
        )
    )