Measure development

[Nazar11]

Hi all, 

I am seeking support with creating a measure: 

ACCEPTANCE CRITERIA: 

calcualte the values in the project_details table from the total Value column when the realization start month is 2025. In the project details table i have multiple years. also the project must be in execution phase, information also stored in project details. Finally I need to apply the right exchange rate within the right year. 

PROBLEM STATEMENT: 

The project_details table contains project currencies that may vary. I need to find the correct exchange rate and convert the values to the appropriate currency using the exchange_rate table.

MATERIALS: 

i have three table:

1. project_details

Project Number	Realization Start Month	Phase	Project Currency	Total Value
002540	Jan 2025	Execution	EUR	25489
002536	Sep 2024	Ideation	GBP	98651651
002533	Sep 2024	Execution	USD	51984651
002492	Nov 2024	Execution	CHF	4684621
002488	Nov 2024	Ideation	EUR	30664202
002487	Jan 2025	Ideation	GBP	27395241,6
002486	Jan 2025	Execution	USD	24126281,2
002485	Jan 2025	Execution	CHF	20857320,8
002484	Sep 2024	Execution	EUR	17588360,4

1. ASP_versions

AOP Year & Tier	Year
2023 Tier 0	2023
2023 Tier 0a	2023
2023 Tier 0b	2023
2024 Tier 0c	2024
2024 Tier 1	2024
2024 Tier 1a	2024
2025 Tier 1b	2025
2025 Tier 1c	2025
2025 Tier 2	2025

1. exchange_rate

Currency From	Currency To	Exchange Rate	Rate Period
EUR	CHF	1,1179	2024
EUR	EUR	1,0000	2024
EUR	GBP	0,8895	2024
EUR	USD	1,1617	2024
EUR	CHF	1,1860	2024
EUR	EUR	1,0000	2025
EUR	GBP	0,8865	2025
EUR	USD	1,2210	2025
EUR	CHF	1,0951	2025

this is the current measure: 

Total Values =

SUMX(

    FILTER(

        project_details,

        project_details[Realization Start Month] >= DATE(2025, 1, 1) &&

        project_details[Realization Start Month] <= DATE(2025, 12, 31) &&

        project_details[Phase] = "Execution"

    ),

    VAR ProjectCurrency = project_details[Project Currency]

    VAR ExchangeRate =

        LOOKUPVALUE(

            exchange_rates[Exchange Rate],

            exchange_rates[Rate Period], [Selected Year ASP],

            exchange_rates[Currency To], ProjectCurrency

        )

    RETURN project_details[Total Value] * ExchangeRate

)

Selected Year ASP =

VALUES(

    ASP_versions[Year]

)

FINAL RESULT: 

I create a graph bar pulling the total value measure and have two filters 

1. exchange_rate[Currency To] -- where I can toggle between different exchange rates 
2. ASP_versions[AOP Year & Tier] -- where I select the right Tier

[Anonymous]

Hi @Nazar11 ,

Thanks to BeaBF  for their interest in this issue. I have some other thoughts to add:

(1) We can create a table.

project_details_2025 = FILTER(
        project_details,
        project_details[Realization Start Month] >= DATE(2025, 1, 1) &&
        project_details[Realization Start Month] <= DATE(2025, 12, 31) &&
        project_details[Phase] = "Execution"
    )

(2) We can create a measure.

Measure =
VAR _value =
    SUM ( 'project_details_2025'[Total Value] )
VAR _rate =
    CALCULATE (
        SUM ( 'exchange_rate'[Exchange Rate] ),
        FILTER (
            ALL ( 'exchange_rate' ),
            [Currency To] = SELECTEDVALUE ( 'exchange_rate'[Currency To] )
                && [Rate Period] = SELECTEDVALUE ( ASP_versions[Year] )
        )
    )
RETURN
    _value * _rate

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[BeaBF]

@Nazar11 Hi! Try adjust the measure like:

Total Values =
SUMX(
FILTER(
project_details,
YEAR(project_details[Realization Start Month]) = 2025 &&
project_details[Phase] = "Execution"
),
VAR ProjectCurrency = project_details[Project Currency]
VAR SelectedCurrencyTo = SELECTEDVALUE(exchange_rate[Currency To])
VAR SelectedYear = SELECTEDVALUE(ASP_versions[Year])
VAR ExchangeRate =
LOOKUPVALUE(
exchange_rate[Exchange Rate],
exchange_rate[Currency From], ProjectCurrency,
exchange_rate[Currency To], SelectedCurrencyTo,
exchange_rate[Rate Period], SelectedYear
)
RETURN project_details[Total Value] * ExchangeRate
)

BBF