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 )

     

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

See if this works:

Sum Rate = 
SUM('Table'[Daily Rate])
Filled Value =
VAR _LNB =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Date], [Sum Rate] ),
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
    )
RETURN
    CALCULATE ( [Sum Rate], FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = _LNB ) )

I've attached the sample PBIX file

danielhough's avatar
danielhough
Helper II
3 years ago

Your PBI file doesnt load for me becasue Im on an older version of PBI, but heres the error im getting

 

 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    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

      Hey Paul! What I did was create a summarized/filteres table from the main table and utitluze your code above and it worked!!

    • 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

      To Close the Loop, I used this code and I got exactly what I need:

      DAILY_RATE_FILLED = 
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'[RT_RATE_INDEX_CCY] = EARLIER ( '3. Rates_table_MMMF'[RT_RATE_INDEX_CCY] )
                && 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'[RT_RATE_INDEX_CCY] = EARLIER ( '3. Rates_table_MMMF'[RT_RATE_INDEX_CCY] )
        )
    )