Create a column that calculate based on the condition of each date having one or two distinct values

[k943]

Hi,

I would like to create a column E (in the picture below), that multiply amount in GBP with the exchange rate for each document date. The condition is if a document date has both exchange rate type MA and MC, then only multiply amount in GBP by the MA's exchange rate, otherwise, if a document date only has one exchange rate type, then multply amount in GBP by that rate. 

In the example below, Jan 31 has both MA and MC exchange rate types, so the amount in Eur is 100 * 1.22 (MA's rate). April 30 only has one exchange rate type, so the amount in Eur is 102 * 1.23. Thank you very much.

[PhilipTreacy · Accepted solution]

Hi @k943 

Download PBIX file with the example below

Create a new column with this

Amount in Eur = 

VAR _rates = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Exchange Rate Type] <> "" && 'DataTable'[Document Date] = EARLIER('DataTable'[Document Date])))

RETURN

IF(_rates = 2, IF('DataTable'[Exchange Rate Type] = "MA" , [Exchange Rate] * [Amount in GBP], BLANK()), [Exchange Rate] * [Amount in GBP])

Regards

Phil

[Anonymous · Accepted solution]

Hi @k943 ,

Your solution is great, @PhilipTreacy . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

While your requirement is to create a calculated column to fulfill the requirement, my suggestion is to create a measure.

In Power BI, a measure performs better than a calculated column and it doesn't take up memory space. So it is better to use a measure when you can fulfill the same requirement.

Create a measure:

Amount in Eur = 
VAR _count = CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table','Table'[Document Date])
)
RETURN 
IF(
    _count = 1 || MAX('Table'[Exchange Rate Type]) = "MA",
    MAX('Table'[Amount in GBP]) * MAX('Table'[Exchange Rate]),
    BLANK()
)

The page effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[PhilipTreacy · Accepted solution]

Hi @k943 

Download PBIX file with the example below

Create a new column with this

Amount in Eur = 

VAR _rates = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Exchange Rate Type] <> "" && 'DataTable'[Document Date] = EARLIER('DataTable'[Document Date])))

RETURN

IF(_rates = 2, IF('DataTable'[Exchange Rate Type] = "MA" , [Exchange Rate] * [Amount in GBP], BLANK()), [Exchange Rate] * [Amount in GBP])

Regards

Phil