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

Thats correct, the Unique Key is essentially the Month/Year/Code that connects to a 1:1 match with a Rate table. This case here, there was no entry for August, because the rate was unchnaged from July. so the result is all blanks until the rate chaged in September

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