Forum Discussion

sandeep_mawri's avatar
sandeep_mawri
Frequent Visitor
8 years ago
Solved

Dynamic Filtering and Comparing with different Time Periods

Hi,   I have an fact table Fact_Sales which contains all the transaction amount and the another fact Fact_ExchangeRate with different year exchange rate for different currencies.I have to create a ...
Show and Tell
  • Floriankx's avatar
    Floriankx
    8 years ago

    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.

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Sumx adds up values row by row.

You need the two variables because I assume (you haven't given us any structure of your tables so far) you have monthly exchange rates. So have you have to multiply each row by the relevant ExR for this actual month and year.

If you the slice by year and/or month you have the correct value although there are 12 relevant rates per year.

sandeep_mawri's avatar
sandeep_mawri
Frequent Visitor
8 years ago

Floriankx I agree for the explanation for Sumx.

 

For Example 1st Time Period Selected is 2018 it will show the transaction amount for the Year 2018 and if the 2nd timeperiod selected is 2017 so it will calculate 2018 transaction amount multiplied by the 2017 Exchange rate for that particular currency.

 

Can you please explain how ill you achieve above scenario with the help of 2 variables that you have created.

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    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.

    • sandeep_mawri's avatar
      sandeep_mawri
      Frequent Visitor
      8 years ago

      Hi Floriankx ,

      Thanks currently implementing the logic will let you know if it works.

       

      Regards,

      Sandeep M.