Data Calculation

[pxg08680]

Hi Everyone, hope someone helps me with this.

I have few columns like

Date,

Currency,
SPVar,

SHVar,

Xvar,

X_LVar. All the variances are in percentages. 

So what I need is, I want to calculate 

(Number of days SPVar has crossed (+5% or -5%) for each currency)/(Total Number of days)--->in last two months.

The same for last 4 months.

For example: SPVar might be more than (+5% or -5%) in last two months = 12 days(For currency1)

                     Total number of days in last two months is = 60 days(assumption)

                    SPVar might be more than (+5% or -5%)in last two months = 8 days(For currency2)

                    Total number of days in last two months is = 60 days(assumption)

And I need to calulate this for SPVar, SHVar,XVar,X_LVar as rows.

So my table would have columns like

1.category(Which is SPVar,SHVar,XVar,X_LVar)

2.Currency

3.Last 2 Months

4.Last 4 Months

This is really an important work for me.

I have attached an image which shows the existing data for last 2 months. But I snipped it to only few rows.

Thank you.

[v-jiascu-msft]

Hi @pxg08680,

Please share a dummy sample. For example, you can replace the currency code with "ABCD...". We need the data and its structure to write a DAX formula. You can upload it to a cloud drive and share the download link here.

Please mask the confidential parts.

Best Regards,

Dale

[pxg08680]

https://docs.google.com/spreadsheets/d/1D7Kg_4UVn21HwHAXmVYpy9p4fAq-bkk67cIt4jcMUCI/edit?usp=sharing

[parry2k]

@pxg08680 define last two months, last 60 days from exchange date, or if exchange date is in Jan 2018, last two months are Nov/Dec 2017?

[pxg08680]

---

@parry2k wrote:

@pxg08680 define last two months, last 60 days from exchange date, or if exchange date is in Jan 2018, last two months are Nov/Dec 2017?

---

***ExchangeRateDate > DATEADD(Month,-2,(select max(ExchangeRateDate)from [dbo].[vw_ConsumerFXVar])**

This is what I was using to validate my data back in SQL. The abouve where query gives me all the dates from Today to begining of Nov.

In the same way Last 4 Months would be from today to begining of September.

[parry2k]

SPVar Crossed Limit in Last 2 Months = 
var dt = MAX(Sheet1[ExchangeRateDate]) - 1 
var lastNumberofDays = 2
return
  Calculate(CountRows(Sheet1), 
    Filter(ALLEXCEPT(Sheet1, Sheet1[CurrencyCode]),
            (Sheet1[SurfaceProvariance] >= 0.05 || Sheet1[SurfaceProvariance] <= -0.05)
    ),
  DATESINPERIOD(Sheet1[ExchangeRateDate], dt, -lastNumberofDays, month)
  
)

make highlighted changes and that will do it.

[pxg08680]

Is this a measure...??

Second thing is I need SPvar as a row not a column. Unpivoting could be used here.

Thanks Parry2k

[parry2k]

yes it is a measure. what output you are looking for?

[pxg08680]

[parry2k]

yes, do it, add table visual, drop currency, exchange rate and two measures i gave you and should do what you are lookign for, not sure what i'm missing here.

[pxg08680]

SP Var % Crossed in Last 2 months = Divide([SPVar Crossed Limit in Last 2 Months], 60, 0)*** This can be used as a column not as a row.

But according to the image, 

SP

SH

X

XL are rows and

 last 2 Months

last 4 Months are columns.

[parry2k]

I'm still not sure, but you can create matrix visual and in format, values -> turn on "shown on rows"

[parry2k]

here is the measure assuming last 60 days (excluding current date)

SPVar Crossed Limit in Last 2 Months = 
var dt = MAX(Sheet1[ExchangeRateDate]) - 1 
var lastNumberofDays = 60
return
  Calculate(CountRows(Sheet1), 
    Filter(ALLEXCEPT(Sheet1, Sheet1[CurrencyCode]),
            (Sheet1[SurfaceProvariance] >= 0.05 || Sheet1[SurfaceProvariance] <= -0.05)
    ),
  DATESINPERIOD(Sheet1[ExchangeRateDate], dt, -lastNumberofDays, day)
  
)

Another measure to get %

SP Var % Crossed in Last 2 months = Divide([SPVar Crossed Limit in Last 2 Months], 60, 0)

Drop table visual, add currency, exchange date and above two measures, take a look at the result. You can tweak it as you see fit.

[Greg_Deckler]

Can you post some of your original data or a sample of it? But I would think that you would want to use something like:

https://msdn.microsoft.com/en-us/library/ee634539.aspx