Forum Discussion
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!
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 )
21 Replies
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.
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
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 )
