Dynamic Filtering and Comparing with different Time Periods

[sandeep_mawri]

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 solution where i have 2 Time period 1st Time period is to show the Transaction for that particular year and the second time period filter to compare it with.

The comparion Transaction will be calculated as follows The 1st time period transaction amount multiplied by the for that specific Currency Exchange rate from 2nd timeperiod selected by the user .

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.

In Fact_Sales and Fact_ExchangeRate Fact Tables Month is the grain.

Please suggest.

Regards,

Sandeep M

[Floriankx]

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]

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]

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]

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]

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]

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 · Accepted solution]

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]

Hi Floriankx ,

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

Regards,

Sandeep M.

[sandeep_mawri]

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 solution where i have 2 Time period 1st Time period is to show the Transaction for that particular year and the second time period filter to compare it with.

The comparion Transaction will be calculated as follows The 1st time period transaction amount multiplied by the for that specific Currency Exchange rate from 2nd timeperiod selected by the user .

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.

Please suggest.