Get earlier Rate in next month if it is blank

[jay_patel]

Hello All,

I have a dataset as mentioned below, here OU is coming from Companymst, Month-year from Calendar and Rate from Exchange Rate Table, what i want is if any month-year have blank rate then it should carry earlier Rate for that OU.

OU	Month-year	Rate
ABC	Apr-24	3
ABC	May-24	4
ABC	Jun-24
DEE	Apr-24	5
DEE	May-24
DEE	Jun-24

Result Column/measure:

Filled Rate

3

4

4

5

5

5

Thanks in advance.

[ryan_mayu]

@jay_patel 

you can try this

Measure =

VAR _date=maxx(FILTER(all('Table'),'Table'[OU]=max('Table'[OU])&&'Table'[Month-year]<max('Table'[Month-year])&&not(ISBLANK('Table'[ Rate]))),'Table'[Month-year])

return if(ISBLANK(max('Table'[ Rate])),maxx(FILTER(all('Table'),'Table'[OU]=max('Table'[OU])&&'Table'[Month-year]=_date),'Table'[ Rate]),max('Table'[ Rate]))

[MNedix]

Heya,

In PowerQuery, simply right-click on the column --> Fill --> Down

[Kedar_Pande · Accepted solution]

Measure to Fill Blank Rates:

Filled Rate = 
VAR CurrentRate = MAX('Exchange Rate Table'[Rate])
RETURN 
IF (
NOT ISBLANK(CurrentRate),
CurrentRate,
CALCULATE(
MAX('Exchange Rate Table'[Rate]),
FILTER(
ALL('Calendar'),
'Calendar'[Month-Year] < MAX('Calendar'[Month-Year]) 
&& 'Companymst'[OU] = MAX('Companymst'[OU])
)
)
)

This approach will ensure that any missing rate for a Month-Year will be filled with the last available rate from earlier months within the same OU

[jay_patel]

Hi @Kedar_Pande 

It is not working fine actually, instead of the earlier date it is taking max Exchange Rate from entire table for specific month.

for example filled rate should be, but someow it is taking max exchange rate from entire table for month - Oct. if i had max rate in Apr-2024 then it will take exchange rate from Apr. instead of earlier date for every month.

Appreciate your efforts.please suggest another solution

OU	Date	Rate	filled rate
ABC	01-Oct-24	3	3
ABC	02-Oct-24	4	4
ABC	03-Oct-24		4
ABC	04-Oct-24	5	5
ABC	05-Oct-24		5
ABC	06-Oct-24		5

[bhanu_gautam]

@jay_patel , You can create a calculated column for this using

Filled Rate =
VAR CurrentOU = 'Table'[OU]
VAR CurrentMonthYear = 'Table'[Month-year]
RETURN
CALCULATE(
LASTNONBLANK('Table'[Rate], TRUE),
FILTER(
'Table',
'Table'[OU] = CurrentOU &&
'Table'[Month-year] <= CurrentMonthYear
)
)

[jay_patel]

Hi @bhanu_gautam 

Thanks for you efforts!

But here i can't create a calculated column, because I'm not sure i do have every month-year in Exchange Rate Table, i want a measure where every month-year is coming from calendar.