Get EOMONTH Currency RATE for accumulated balance

[Digger]

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])

PBIX https://easyupload.io/554byn   
password: 123456

[Anonymous]

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]

@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