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

Thank you so much for your continued help! the data I sent earler was a cut out of my overall model. In my model, my data is being retrieved from an oracle database and the table I provided is the result of a daily table:

 

3. RATES_TABLE_MMMF = 
    Var Cal = 
    CALENDAR(DATE(YEAR(TODAY())-1,1,1),max('PS_RT_RATE_TBL'[EFFDT]))
    Var Des = 
    SUMMARIZE('PS_RT_RATE_TBL',PS_RT_RATE_TBL[RT_RATE_INDEX_CCY])
    VAR TAB = GENERATE(Cal,Des)
    Return
    TAB

Other columns:

DAILY_RATE = RELATED(PS_RT_RATE_TBL[RATE_MULT])
Unique Key = MONTH([date])&YEAR([date])&[RT_RATE_INDEX_CCY]
DAILY_TABLE_KEY = [DATE]&[RT_RATE_INDEX_CCY]

 

How may I adapt your code to these paramaters? 

 

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

The Power Query method need the tables in Power Query, and Calculated tables are no accesible in PQ . So we need the imported 

'PS_RT_RATE_TBL'

to be able to build the whole table  in PQ instead of using DAX