Forum Discussion

Digger's avatar
Digger
Icon for Post Patron rankPost Patron
1 year ago

Get EOMONTH Currency RATE for accumulated balance

HOW Get EOMONTH Currency RATE for accumulated balance?

_Sum_EUR_accum =
CALCULATE(
    [_Sum_EUR],
    FILTER(
        ALL('__Calendar'[Date]),
        '__Calendar'[Date] <= MAX('__Calendar'[Date])
    )
)
_Sum_Currency_accum =
CALCULATE(
    [_Sum_Currency],
    FILTER(
        ALL('__Calendar'[Date]),
        '__Calendar'[Date] <= MAX('__Calendar'[Date])
    )
)

_LastMday_currency_rate =
VAR CurrentDate = MAX('__Calendar'[Date])  -- Get the current date from the calendar
VAR CurrentCode = MAX(Table1[Currency_Code])  -- Get the current currency code

RETURN
CALCULATE(
    MAX(PP_Currency_rate[amount_2]),  -- Get the latest available rate  EACH MONTH LAST DAY (EOMONTH)
    FILTER(
        PP_Currency_rate,
        PP_Currency_rate[name_2] = CurrentCode &&
        PP_Currency_rate[date] = CurrentDate  -- Take the latest available rate LATEST SELECTED DAY IN MONTH SELECTED IN DATE SLICER
    ),
    ALL(Table1)  -- Ignore accumulated balance filters
)
_Sum_EUR_RECALCULATED = DIVIDE([_Sum_Currency_accum],[_LastMday_currency_rate])
 
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Digger 

     

    There is nothing wrong with your measures, I think the blank portion of the matrix is due to the fact that you are using date fields in both the rows and columns causing a change in the calculated range of the measures.

     

    I would suggest that you consider removing the date fields from the columns in the matrix visual. Set the slicer style to “Less than or equal to” in the Visual Formatting settings, and view the currency rate for the accumulated balance up to the selected year.

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Digger's avatar
      Digger
      Icon for Post Patron rankPost Patron
      1 year ago

      Anonymous I cant remove date fields from the columns in the matrix visual, also i need add one more field and slicer KODAS_ZS which also does not shown in matrix visual. But i need see ALL info!!


      because 0 zeroes show on calculating Sum_EUR, but on Sum_Currency it can be not equal to 0 (because on wrong rate used when countd Sum_Eur), that what i need to find!!

      Also Values should be shown for all fields, but here some missing