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
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!
Solved! Go to 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 )
Proud to be a Super User!
Paul on Linkedin.
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
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
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 )
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:
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] )
)
)
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
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)
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
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
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?
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"
New file attached
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
Proud to be a Super User!
Paul on Linkedin.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
84 | |
76 | |
64 |
User | Count |
---|---|
145 | |
109 | |
109 | |
102 | |
96 |