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

Hello,

 

what are your columns of Fact_ExchangeRate?

I assume Year, Month, Currency, Rate?

 

In Fact_Sales you probably have a Date column.

 

I think Measure for Native Value is fine?

 

For Foreign Value you could try

Foreign_Value:=
SUMX(Fact_Sales;
 VAR SalesMonth=Month(Fact_Sales[Date])
 VAR SalesYear=Year(Fact_Sales[Date])
 VAR EXR=Calculate(Max(fExR[ExR]),Filter(fExR,fExR[Year]=SalesYear && fExR[Month]=SalesMonth))
Return
 Fact_Sales[Amount]*EXR)

Attention, the MONTH formula gives you an integer from 1 to 12 so your ExR table should contain Month numbers.

I shortened Fact_ExchangeRate to fExR for readability. The Max in the Calculate is there because you have to aggregate the values somehow although the filter should return only one value.

 

It's coded freestyle but I hope it works

sandeep_mawri's avatar
sandeep_mawri
Frequent Visitor
8 years ago

Hi Floriankx,

 

Thanks.

 

But for clarification.

 

Foreign_Value:=
SUMX(Fact_Sales;
VAR SalesMonth=Month(Fact_Sales[Date])
VAR SalesYear=Year(Fact_Sales[Date])
VAR EXR=Calculate(Max(fExR[ExR]),Filter(fExR,fExR[Year]=SalesYear && fExR[Month]=SalesMonth))
Return
Fact_Sales[Amount]*EXR)

 

In this formular can you explain me what two variables VAR SalesMonth and SalesYear have the value.

And how this 2 variables will be used to show the Exchange Rate for different year.

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