Forum Discussion

Truelearner's avatar
Truelearner
Helper III
6 years ago
Solved

need help with dax

I have the table below where in i have original currency and transaction currency along with effective date and exchnage rate , 

 

i have this requirement where in which i want to create a table with with highest date when i have two or more exchange rates within same month and i want to have a column in which i would like to have the previous month exchnage rate if the current exchnage rate is not available.

 

@mgwena @cham @amitchandak @Greg_Deckler @Mariusz 

 

Input table :

 

Original currTransaction CurrEffective DateExchangerate
USDINR02-01-202071
USDINR18-01-202072.05
USDEUR07-01-202014
USDINR04-02-202071.5
USDCAD07-02-20201.01
USDINR03-03-202072

 

Output Table :

 

Original currTransaction CurrEffective DateExchangeratePrevious exchnage rate 
USDINR18-01-202072.05 
USDEUR07-01-20201472.0
USDCAD07-02-20201.01 
USDINR03-03-20207214
Need Help

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Create a MonthNo column first:

    MonthNo = 'Table'[Effective Date].[MonthNo]

    Add an index column in Query Editor.

    Then try this measure:

    check = IF(MAX('Table'[Effective Date])=MAXX(FILTER(ALL('Table'),'Table'[MonthNo] in FILTERS('Table'[MonthNo])),'Table'[Effective Date]),1,0)

    Apply it to original table visual by setting check=1, it shows:

    Then try this measure:

    Previous exchnage rate = CALCULATE(MAX('Table'[Exchangerate]),FILTER(ALLSELECTED('Table'),'Table'[MonthNo]=MAX('Table'[MonthNo])-1))

    The result shows:

    Tips: Based on your logic, i think the expected output is like the above not the screenshot you posted.

            Please check it.

            If i have understood your requirement, please for free to let me know.

            Expect your reply!

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi