Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
GriRim
Frequent Visitor

Summarization of recalculated values

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:

GriRim_0-1747313835087.png

 

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!

1 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.

View solution in original post

4 REPLIES 4
GriRim
Frequent Visitor

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.

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:

GriRim_0-1747738409302.png

 

MFelix
Super User
Super User

Hi @GriRim ,

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.