Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to 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.
Thanks @MFelix , my bad.
Now it should work: https://drive.google.com/file/d/18nizxW82MB3ImM_IM8v8KGHOs4TMWvlF/view?usp=sharing
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:
Hi @GriRim ,
The file is not available is giving a unauthorized error. can you please reshare the link.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
84 | |
82 | |
66 | |
52 | |
46 |
User | Count |
---|---|
100 | |
48 | |
42 | |
39 | |
38 |