Forum Discussion

ekaponkratova's avatar
ekaponkratova
Frequent Visitor
7 years ago
Solved

Calculation when no country is selected

I have the below dataset and user country as a filter. The task is to calculate the revenue - calculated as sum paid in this month -  sum paid in the previous month. It should be calculated in USD. The solution of multiplying the exchange rate in the below table, then, creating a measure using PREVIOUSMONTH and then, doing subtraction doesn't work because of the high exchange rate fluctuation. 

The solution we came with was to calculate the revenue in the local currency. And then, if a user selects Switzerland from the filter, the revenue will be multiplied by the exchange rate for the corresponding country and corresponding date. 

Here are the formulas in practice:

Measures:
 
prior_month_sum_lc = CALCULATE(sum(fact_payment[sum_paid_to_date]),PREVIOUSMONTH(calendar_3[Date]))
 
revenue = sum(fact_payment[sum_paid_to_date])-[prior_month_sum_lc] 
 
revenue_usd = IF(SELECTEDVALUE(fact_payment[country])="Spain",
[revenue] * AVERAGEX(fact_payment,fact_payment[xr_es]),0 )
 

And it is working just fine when you select a particular country, however, I cannot a formula work when no country is selected. I've uploaded the sample at

https://drive.google.com/file/d/1ADLoDNSVBFo-G_SYj30xPH490yTDNXrL/view?usp=sharing.

 

My attempt to do the filter is in the measure final

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi ekaponkratova ,

     

    To update your measure as below.

     

    final_calc =
IF (
    ISFILTERED ( Sheet1[country] ) = FALSE (),
    CALCULATE (
        SUM ( Sheet1[cum sum paid] ),
        DATEADD ( 'calendar_3'[Date], -1, MONTH ),
        Sheet1[country] = "Spain"
    )
)

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    ekaponkratova  - You'll want to do something like SUMX(YourTable[Raw Revenue]*ExchangeRates[Rate])

    Because you need to calculate the value for each day and then sum them up.

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi ekaponkratova ,

     

    To update your measure as below.

     

    final_calc =
IF (
    ISFILTERED ( Sheet1[country] ) = FALSE (),
    CALCULATE (
        SUM ( Sheet1[cum sum paid] ),
        DATEADD ( 'calendar_3'[Date], -1, MONTH ),
        Sheet1[country] = "Spain"
    )
)