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 code in Power Query works for you:

 

 

let
  Source = Excel.Workbook(
    File.Contents("D:\OneDrive\1 Shared web\Comm PBIs\Filled Value\Power BI\Book2.xlsx"),
    null,
    true
  ),
  Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
  #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {
      {"Date", type date},
      {"Unique Key", type text},
      {"DAILY_RATE", type number},
      {"RT_RATE_INDEX_CCY", type text},
      {"DAILY_TABLE_KEY", type text}
    }
  ),
  #"Sorted Rows2" = Table.Sort(
    #"Changed Type",
    {{"RT_RATE_INDEX_CCY", Order.Ascending}, {"Date", Order.Ascending}}
  ),
  #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows2", "DAILY_RATE", "RATE"),
  #"Group" = Table.Group(
    #"Duplicated Column",
    {"RT_RATE_INDEX_CCY"},
    {{"All_Rows", each Table.FillDown(_, {"RATE"}), type table}}
  ),
  #"Expanded All_Rows" = Table.ExpandTableColumn(
    Group,
    "All_Rows",
    {"Date", "Unique Key", "DAILY_RATE", "RT_RATE_INDEX_CCY", "DAILY_TABLE_KEY", "RATE"},
    {"Date", "Unique Key", "DAILY_RATE", "RT_RATE_INDEX_CCY.1", "DAILY_TABLE_KEY", "RATE"}
  ),
  #"Changed Type3" = Table.TransformColumnTypes(
    #"Expanded All_Rows",
    {{"Date", type date}, {"RATE", type number}, {"DAILY_RATE", type number}}
  ),
  #"Reordered Columns" = Table.ReorderColumns(
    #"Changed Type3",
    {
      "RT_RATE_INDEX_CCY",
      "Date",
      "Unique Key",
      "DAILY_RATE",
      "RATE",
      "RT_RATE_INDEX_CCY.1",
      "DAILY_TABLE_KEY"
    }
  )
in
  #"Reordered Columns"

 

 

 

New file attached

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