cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!

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!

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!

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!

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!

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!

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!

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(
{
{"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!

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!

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors