Currency conversion measure is selecting month prior to current context

DAX DAX Calculations
1

The measure below is selecting the month prior to the current context.

The Currency table has the last day of each month with the respective currency and conversion value on each row

The DELIVERY table has daily records.

The measure shown below is returning the conversion value for the respective currency for the month prior. Example would be that DELIVERY context is July, returned currency value is June

I tried some variants of the measure to no avail.

I even tried substituting he following but it didn’t work either

ENDOFMONTH(Currency[Date]) < DELIVERY[ACTUAL_DATE]

Likewise changing the operator from < to > returns the next month from the currency table.

Currency[Date] > DELIVERY[ACTUAL_DATE]

Thanks
J

Currency Conv = 
    
    SUMX( DELIVERY[DELIV_AMOUNT] *
        LOOKUPVALUE( 
            Currency[Value],
            Currency[Currency], DELIVERY[CURRENCY],
            Currency[Date], LASTDATE(
                FILTER(
                    ALL( Currency[Date]), 
                Currency[Date] < DELIVERY[ACTUAL_DATE]
                )
            )
        )
    )

below is the Currency Table, The current context is June but the measure returns the currency values for May

Currency Table

2

Hi @jprlimey, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

@jprlimey,

Try replacing line 10 in your measure with this:

If that doesn’t solve your problem, please post your PBIX, along with the result you want to see, so we can better provide a specific solution.

I hope this is helpful.

  • Brian
4

@jprlimey Few things:

  1. You missed the table part in your measure.
  2. I wouldn’t use LASTDATE in a row context, it will trigger context transition.

As @BrianJ said we would need the file.

5

Hi @jprlimey, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

6

Sorry I was pulled away on another project. I will need to create a sample file to post to aid in resolving this question.

J