If there is no value for a month, use the value of the previous month

[DavidWaters100]

Hi,

I have realised that an exchange rate table does not contain May 2020 and I must use April 2020, ie last populated month.  So I was trying to set up an if statement to say if the ROE is blank, then use the previous month (ie April).  It must work on the test file which can be downloaded by the dropbox link below - I have to use "Related" function to bring in the ROE to the main data table.

Any help greatly appreciated, running out of time for a deadline!

https://www.dropbox.com/s/dgcxlk7wj0qzs0c/roe%20test%20%281%29.pbix?dl=0

thanks

[mahoneypat]

Here is one expression that should work for your column (if I understood what you are looking for).

New ROE =
VAR lastROEdate =
CALCULATE ( MAX ( ROE[Month] ), ALL ( ROE ), NOT ( ISBLANK ( ROE[ROE] ) ) )
VAR ROEvalue =
CALCULATE ( MIN ( ROE[ROE] ), ALL ( ROE ), ROE[Month] = lastROEdate )
RETURN
IF (
ISBLANK ( RELATED ( ROE[ROE] ) ),
ROEvalue * Data[Amount],
RELATED ( ROE[ROE] ) * Data[Amount]
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[DavidWaters100]

Thank you @mahoneypat - this works in my dummy file that I provided a link for, however in the real file there are hundreds of different currencies and I think the " MIN ( ROE[ROE]" part is causing an error for me - I just get a very small number because the smallest ROE has been used. 

Any workarounds please do you think?

[mahoneypat]

Is there a currency column in both the Data and ROE tables?  If so, you can use the same approach as for the selecteddate part - save the currency for that row in the Data table in a variable, and then add that as a filter in the calculate (e.g., ", ROE[Currency] = selectedcurrency" where selectedcurrency is the name of the variable).

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[DavidWaters100]

Hi @mahoneypat,

I think I know what you mean but have not been able to apply the logic correctly!  Below is link to updated file with your solution in and two different currencies.  The currency is in both tables.  Do you think you could tweak it to work with multiple currencies - in this file you will see that the wrong currency rate is returning for May 2020 due to the Min?

Many thanks!

https://www.dropbox.com/s/u56ilqmnhyddics/roe%20test2.pbix?dl=0

[mahoneypat · Accepted solution]

Here you go.  Please confirm this works.  I simplified it a little too, removing the IF() which really wasn't needed.

New ROE col =

VAR lastROEdate =

CALCULATE ( MAX ( ROE[Month] ), ALL ( ROE ), NOT ( ISBLANK ( ROE[ROE] ) ) )

var currentccy = Data[CCY]

VAR ROEvalue =

CALCULATE ( MIN ( ROE[ROE] ), ALL ( ROE ), ROE[Month] = lastROEdate, ROE[CCY]=currentccy )

RETURN

ROEvalue*Data[Amount]

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[DavidWaters100]

I tried but still not quite working for some reason - the new one does not return correct result for older months either, but the one with the IF statement did.  Are you sure we don't need the IF?  I will try to identify why it does not work on my real data set.

Thanks so far

[DavidWaters100]

Ah ha - there are 4 currency "To" options in my real data.  The Min in your solution works when there is only one currency "To" option - so all I needed to do was add another filter to the Calculate function to specify the currency to - "USD" only.  Then the Min does not switch to another currency.

Not sure if that makes sense, but thank you so much for your great solution!

many thanks

David

[mahoneypat]

Glad you fixed it.  I had forgotten you didn't always want the latest one, just when there isn't one available.  So the IF is needed.  Here it is back in.  I also added the currency clause to the first calculate, so it gets the latest date for the correct currency.

New ROE col =

var currentccy = Data[CCY]

VAR lastROEdate =

CALCULATE ( MAX ( ROE[Month] ), ALL ( ROE ), NOT ( ISBLANK ( ROE[ROE] ) ) , ROE[CCY]=currentccy)

VAR ROEvalue =

CALCULATE ( MIN ( ROE[ROE] ), ALL ( ROE ), ROE[Month] = lastROEdate, ROE[CCY]=currentccy )

RETURN

IF (

ISBLANK ( RELATED ( ROE[ROE] ) ),

ROEvalue * Data[Amount],

RELATED ( ROE[ROE] ) * Data[Amount]

)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[DavidWaters100]

thank you @mahoneypat - I did need the IF.  I have checked and believe all OK, thanks again