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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sai_Alkesh
Helper II
Helper II

undefined

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,

2 ACCEPTED SOLUTIONS

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 )

 

vxiaotang_1-1648107277187.png

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.

View solution in original post

Hi @Sai_Alkesh 

In this scenario, you can modify the red part. 

vxiaotang_2-1648191378876.png

-

EARLIER: EARLIER function (DAX) - DAX | Microsoft Docs

vxiaotang_1-1648191023723.png

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.

View solution in original post

6 REPLIES 6
Sai_Alkesh
Helper II
Helper II

@v-xiaotang : Thank you so much. This works !!!

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

vxiaotang_2-1648191378876.png

-

EARLIER: EARLIER function (DAX) - DAX | Microsoft Docs

vxiaotang_1-1648191023723.png

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
Community Support
Community Support

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

vxiaotang_0-1648103459328.png

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.

@Tang 

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 )

 

vxiaotang_1-1648107277187.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors