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

Okay, make sure your tables are not related.

 

EXR:=Max(Fact_ExchangeRate[ExchangeRate]

 

Native_Value:=SUM(Fact_Sales[SalesAmount])

Foreign_Value:=Native_Value*EXR

 

This should work if you have a slicer for currency and currency year.

1st time period is from Fact_Sales and 2nd time period is from Fact_ExchangeRate.

 

You could add HASONEVALUE to EXR measure to make sure one currency and one year is selected.

  • sandeep_mawri's avatar
    sandeep_mawri
    Frequent Visitor
    8 years ago

    Thanx Florianks for the reply but i think this will not solve my problem.

     

    I would like to be more precise this time that i have 2 fact tables and the grain is month. So in Fact_Sales Fact table i have transaction for every month and same is the thing with Fact_ExchangeRate Exchange rate for every currency for every month.

     

    1st Scenario

    So if the user select April 2018 in Ist Tiime period and May 2017 so he must see Transaction Amount for April 2018 and the Comparing Transaction to be calculated should be Transaction Amount for April 2018 Multiplied by Exchange Rate for that currency for May 2017.

     

    2nd Scenario

    If the User select Year 2018 in 1st Time Period and Year 2016 in 2nd Time Period so he must be able to see Transaction Amount for 2018 and Compared Transaction Amount should be calculated as every month Transaction amount of 2018 multiplied by respective month exchange rate of 2016 and then the sum of it.

     

    So taking Max of Exchange Rate will take the max Exchange Rate for that particular Year which will be altogether a calculation mess.

     

    Please Floriankx help me or suggest any solution.

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