Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
danielhough
Helper II
Helper II

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 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!

 

PBI.PNG

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

ccolumn.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

21 REPLIES 21
PaulDBrown
Community Champion
Community Champion

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

filled1.gif

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

danielhough_0-1663863273873.png

 

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 )

ccolumn.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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

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

danielhough_0-1663865889163.png

 

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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 

 

 

danielhough_0-1663871293064.png

 

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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






You're Right, that did fix it, but for some reason its still not populating the blanks with 1.1404

 

danielhough_0-1663933440706.png

 

 

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

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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"

 

 

Fill.png

 

New file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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? 

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






davehus
Memorable Member
Memorable Member

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.

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. 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.