Carry Forward Values until Change

[danielhough]

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 need a DAX to carry this 1.14043 figure over the blank rows until august 1 where it is now 1.55169.. The table name is, 3.RATES_TABLE_MMMF

Thank You!

[PaulDBrown]

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]

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

[PaulDBrown · Accepted solution]

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]

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] )
        )
    )

[danielhough]

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

[danielhough]

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

[PaulDBrown]

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]

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]

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]

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] )
        )
    )

[PaulDBrown]

Whcih field sets the Rate? I'm going to do in Power Query which is much mor efficient for these types of tasks

[danielhough]

The rate is brought into the table via a related forumla based on the uniuqe Key

[PaulDBrown]

So the values have to be filled in based on date and Unique key?

[danielhough]

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]

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]

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]

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

[davehus]

Hi @danielhough , Are you able to sort the table in powerquery and use the fill down function to fill the null values?

Did I help you today? Please accept my solution and hit the Kudos button.

[danielhough]

Hey! So this is a calulcated table so I don't see it in Power Query, but I essentially need to fill "up" the numbers from old to new. 

[davehus]

Maybe something like this might help?

https://community.powerbi.com/t5/Desktop/Find-next-value-by-date/td-p/503446

Did I help you today? Please accept my solution and hit the Kudos button.