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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!

1 ACCEPTED SOLUTION
Community Champion

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

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.

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

``````

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.

Helper II

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

Community Champion

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

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.

Helper II

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] )
)
)``````
Helper II

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

Helper II

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

Community Champion

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.

Helper II

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

Community Champion

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.

Helper II

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

Community Champion

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.

Helper II

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

Community Champion

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.

Helper II

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

Community Champion

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

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.

Helper II

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?

Community Champion

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.

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.

Helper II

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.

Memorable Member

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors