Dynamic Filtering and Comparing with different Time Periods

Hello,

My bad. The formula I gave is supposed to multiply the ExR of the Month and Year of the Date without deviations due to manuel slicer.

Foreign_Value:=
IF(HASONEVLAUE(fExR[Currency]), //condition 1
 IF(HASONEVALUE(Fact_Sales[Year])&&HASONEVALUE(fExR[Year]) //condition 2
  IF(HASONEVALUE(Fact_Sales[Month])&&HASONEVALUE(fExR[Month]) //conditon 3
  SUM(Fact_Sales[Amount])*Max(fExR[ExR]), //true part
  SUMX(Fact_Sales,
   VAR SalesMonth=Month(Fact_Sales[Date])
   VAR EXR=Calculate(Max(fExR[ExR]),Filter(fExR,fExR[Month]=SalesMonth))
   Return
   Fact_Sales[Amount]*EXR),
 Blank()), //else condition 2
Blank()) //else condition 1

 I assume you either have two select to times month or two times year, no mixture hopefully.

So I hope we are getting closer. HASONEVALUE ensures that there is only one value of the column afterwards selected.

What I did not check: If user selects more than one month HASONEVALUE returns false and assumes whole year beeing selected.

So formula would get more complex to ensure that either one ore no month is selected.

So if months are selected there is only one value for ExR so simple muliplication is fine.

If whole year is selected you have to use SUMX because there is one rate for each month.

I hope we are getting closer.