Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Need help with the following:
I have 2 tables:
Table Currency
Identifier field LCurrency FCurrency FX Rate Type Period
yyyymm EUr GBp 0.8545 AVRS 20214
USd GBp 1.1684 AVRS 20214
JPy EUr 0.88018 RNMJ 20228
Table Sales
Identifier Item Currency Sales Period Calculated Column
yyyymm 123456 USd 10000 20214 8558.712
234567 USd 8000 20213 6846.97
3456789 EUr 2000 20216 2340.55
i have the following DAX for the calculated Column in Table Sales
'Sales'[Sales] *
DIVIDE(1,
CALCULATE(
FIRSTNONBLANK('Currency'[FXRate], TRUE() ),
FILTER(
'Currency',
'Currency'[LCurrency]= 'Sales'[Currency] &&
'Currency'[FCurrency]= "GBP" &&
'Currency'[TypeT]= "AVRS" &&
'Currency'[Period]= "20214")
)
)
The issue is, i get the calculated column values, where the period is 20214, but not for other rows, where the period is not '20214'. Values in Red font in the calculated column are not getting calculated in PBI.
How can i get the values for all rows.
Appreciate your help.
Thanks,
Solved! Go to Solution.
Hi @Sai_Alkesh
try this calculated column
ColumnTest =
VAR _currency =
CALCULATE (
MAX ( 'Currency'[FXRate] ),
FILTER (
ALL ( 'Currency' ),
'Currency'[LCurrency] = EARLIER ( 'Sales'[Currency] )
)
)
RETURN
'Sales'[Sales] * DIVIDE ( 1, _currency )
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Sai_Alkesh
In this scenario, you can modify the red part.
-
EARLIER: EARLIER function (DAX) - DAX | Microsoft Docs
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@v-xiaotang : as a follow up, if i want to put more conditions, how do i do that ?
The above works, if there is only condition to filter. But if i want to filter more than one column, how will do it?
Appreciate your help !!
Hi @Sai_Alkesh
In this scenario, you can modify the red part.
-
EARLIER: EARLIER function (DAX) - DAX | Microsoft Docs
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Sai_Alkesh
Thanks for reaching out to us.
Here are some questions to be confirmed:
"The issue is, i get the calculated column values, where the period is 20214, but not for other rows, where the period is not '20214'. Values in Red font in the calculated column are not getting calculated in PBI."
-
what are the correct values for other rows? Currency & Period of row1/row2/row3 are different, will they affect calculation results? If yes, for row2/3, how to calculate ( Math formula)?
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Thank you for reaching out.
The formula is Sales * DIVIDE(1, FX Rate (from currency table), where Sales.Currency=Currency.LCurrency
For row 2, the example will be 8000 * 1/1.1684 = 6846.97 and
For row 3, the exmaple will be 2000 * 1/0.8545 = 2340.55
Hi @Sai_Alkesh
try this calculated column
ColumnTest =
VAR _currency =
CALCULATE (
MAX ( 'Currency'[FXRate] ),
FILTER (
ALL ( 'Currency' ),
'Currency'[LCurrency] = EARLIER ( 'Sales'[Currency] )
)
)
RETURN
'Sales'[Sales] * DIVIDE ( 1, _currency )
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |