Summarization of recalculated values

[GriRim]

Hi all,

Can someone look through what I'm doing wrong in this case.

Basiaclly I want to calculate Gross profit (GP) in EUR using GP in local currency, but rate is selected by users seleted period.

Rate is always the latest that year's rate:

if a user selects Feb 2025 and Mar 2025 - rate is used Mar 2025

if a user selects Aug2024, Oct 2024, Dec 2024 -  it uses 2024 Dec rate

if a users selects both years - rate should be choosen by the latest that year rate. If a user selects Oct 2024, Nov 2024, Jan 2025 and Feb 2025 - rate is selected: for 2024 - Nov 2024 and for 2025 - Feb 2025.

I managed to calculate proper rate calculation with this DAX:

GP in EUR = 
VAR LC = SUM('Fact Customer Profitability'[CUST_INV_AMT])
VAR Lastdateselected = 
CALCULATE(
    MAX('Dim Date'[FINANCIAL_DATE]),
    FILTER(
        ALLSELECTED('Dim Date'),
        'Dim Date'[FINANCIAL_YEAR] = MAX('Dim Date'[FINANCIAL_YEAR])
    ))
VAR aggtable =
    FILTER (
        'Fact Exchange Rate',
        'Fact Exchange Rate'[EXCHANGE_RATE_TYPE] = "ACTUALS"
            && 'Fact Exchange Rate'[EXCHANGE_RATE_CURRENCY_CODE_ISO_FROM] = "EUR"
                   &&  Lastdateselected >= 'Fact Exchange Rate'[EXCHANGE_RATE_START_DT]
                     && Lastdateselected <= 'Fact Exchange Rate'[EXCHANGE_RATE_END_DT]
    )
VAR Currencyexchangetable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Fact Customer Profitability',
            'Dim Date'[FINANCIAL_DATE],
            'Dim Currency Name'[CURRENCY_ISO_CODE],
            'Fact Customer Profitability'[CUSTOMER_PROFITABILITY__HASH_ID],
            'Fact Customer Profitability'[CUST_INV_AMT]
        ),
        "@rate",
            /*VAR current_date = 'Dim Date'[FINANCIAL_DATE]*/
            VAR CurrencyID = 'Dim Currency Name'[CURRENCY_ISO_CODE]
            RETURN
                IF (
                    'Dim Currency Name'[CURRENCY_ISO_CODE] = "EUR",
                    1,
                    MAXX (
                        FILTER (
                            aggtable,
                                'Fact Exchange Rate'[EXCHANGE_RATE_CURRENCY_CODE_ISO_TO] = CurrencyID
                        ),
                        'Fact Exchange Rate'[EXCHANGE_RATE_INVERSE_MULTIPLIER]
                    )
                )
    )
VAR GC =
    SUMX ( Currencyexchangetable, 'Fact Customer Profitability'[CUST_INV_AMT] * [@rate] )
RETURN
   GC 

The propblem is that when it summarize different months I do not get the proper total amount:

This is example pbix if someone whants to play around: https://docs.google.com/spreadsheets/d/1OkfeJIyIUAEPP3N-_lM5_v8Br82fb_bh/edit?usp=sharing&ouid=11660... 

Thanks in advance!

[GriRim]

Thanks @MFelix , my bad.

Now it should work: https://drive.google.com/file/d/18nizxW82MB3ImM_IM8v8KGHOs4TMWvlF/view?usp=sharing 

[v-kathullac · Accepted solution]

Hi @GriRim ,

Thank you for reaching out to Microsoft Fabric Community Forum.

can you try with below measure and let us know if you need anything 

GP in EUR =
VAR LastDateSelectedPerYear =
CALCULATETABLE (
ADDCOLUMNS (
VALUES('Dim Date'[FINANCIAL_YEAR]),
"MaxDate", CALCULATE (
MAX('Dim Date'[FINANCIAL_DATE]),
ALLSELECTED('Dim Date'),
'Dim Date'[FINANCIAL_YEAR] = EARLIER('Dim Date'[FINANCIAL_YEAR])
)
)
)

VAR ExchangeTable =
FILTER (
'Fact Exchange Rate',
'Fact Exchange Rate'[EXCHANGE_RATE_TYPE] = "ACTUALS"
&& 'Fact Exchange Rate'[EXCHANGE_RATE_CURRENCY_CODE_ISO_FROM] = "EUR"
)

VAR DataTable =
ADDCOLUMNS (
'Fact Customer Profitability',
"@Rate",
VAR CurrencyID = RELATED('Dim Currency Name'[CURRENCY_ISO_CODE])
VAR DateUsed =
CALCULATE (
MAX('Dim Date'[FINANCIAL_DATE]),
FILTER (
ALLSELECTED('Dim Date'),
'Dim Date'[FINANCIAL_YEAR] = RELATED('Dim Date'[FINANCIAL_YEAR])
)
)
RETURN
IF (
CurrencyID = "EUR",
1,
CALCULATE (
MAX('Fact Exchange Rate'[EXCHANGE_RATE_INVERSE_MULTIPLIER]),
FILTER (
ExchangeTable,
'Fact Exchange Rate'[EXCHANGE_RATE_CURRENCY_CODE_ISO_TO] = CurrencyID
&& 'Fact Exchange Rate'[EXCHANGE_RATE_START_DT] <= DateUsed
&& 'Fact Exchange Rate'[EXCHANGE_RATE_END_DT] >= DateUsed
)
)
)
)

RETURN
SUMX (
DataTable,
'Fact Customer Profitability'[CUST_INV_AMT] * [@Rate]
)

Regards,

Chaithanya.

[GriRim]

Thanks for the effort, but it seems your calculation lose the logic, that it should take into account latest selected period's rate for that year:

[MFelix]

Hi @GriRim ,

The file is not available is giving a unauthorized error. can you please reshare the link.