Show fluctuation Qtr end until today

[goldbergeddie]

Hi,

I'm trying to measure the fluctuation of a currency (i.e. USD=EUR) QTR to date.

I have a database of all the rates and i'm trying to set a formula to show the rate of the last day of the previous qtr in order to compare it to today's rate.

Any ideas?

[v-yulgu-msft · Accepted solution]

Hi @goldbergeddie,

In Dates table, please add a Quarter column. There is a one to many relationship between Dates table and M_Rate_Report.

Quarter = Dates[Date].[QuarterNo]

Suppose you already have Year column and Month column in M_Rate_Report table. Now, please add a Quarter column.

Quarter = LOOKUPVALUE(Dates[Quarter],Dates[Date],M_Rate_Report[Date])

Then, in M_Rate_Report table, create a two calculated columns which returns relative values of the last month of previous quarter.

Last R rte =
LOOKUPVALUE (
    M_Rate_Report[R rte],
    M_Rate_Report[Year], M_Rate_Report[Year],
    M_Rate_Report[Quarter], M_Rate_Report[Quarter] - 1,
    M_Rate_Report[Month], CALCULATE (
        MAX ( M_Rate_Report[Month] ),
        FILTER (
            M_Rate_Report,
            M_Rate_Report[Year] = EARLIER ( M_Rate_Report[Year] )
                && M_Rate_Report[Quarter]
                    = EARLIER ( M_Rate_Report[Quarter] ) - 1
        )
    )
)

Last conversion Factor =
LOOKUPVALUE (
    M_Rate_Report[Conversion Factor],
    M_Rate_Report[Year], M_Rate_Report[Year],
    M_Rate_Report[Quarter], M_Rate_Report[Quarter] - 1,
    M_Rate_Report[Month], CALCULATE (
        MAX ( M_Rate_Report[Month] ),
        FILTER (
            M_Rate_Report,
            M_Rate_Report[Year] = EARLIER ( M_Rate_Report[Year] )
                && M_Rate_Report[Quarter]
                    = EARLIER ( M_Rate_Report[Quarter] ) - 1
        )
    )
)

Then, create measure as below:

last month in last quarter =
SUMX (
    M_Rate_Report,
    M_Rate_Report[Last R rte] / M_Rate_Report[Last conversion Factor]
)

R Rate = SUMX(M_Rate_Report,M_Rate_Report[R rte]/[Conversion Factor])

Trend = CALCULATE([R Rate]/[last month in last quarter]-1)

Regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @goldbergeddie,

Have you worked it out? If so, would you please kindly mark the helpful reply as an answer or sharing your solution?

Regards,
Yuliana Gu

[v-yulgu-msft]

Hi @goldbergeddie,

It's hard to imagine your table structure and your expected result. Would you please illustrste your scenario with sample data? Please follow the suggestion mentioned in this article to better describe your requirement so that we can test for you.

Regards,
Yuliana Gu

[goldbergeddie]

Hi @v-yulgu-msft,

Thanks for your comments.

I have this database where I have a list of currencies. Every month-end a final rate is being set and it will be used for the whole month.

For example:

 

EUR rate for May 2017 is 1.12225.

I'm trying to create 2 caculations:

1. Fluctuation month over month of the currency - which I have successfully created:

R Rate = SUMX(M_Rate_Report,M_Rate_Report[R rte]/[Conversion Factor]) 

Previous Month = CALCULATE([R Rate],PREVIOUSMONTH(Dates[Date]))

Trend = CALCULATE([R Rate]/[Previous Month]-1)

2. Fluctuation QTD - this is where i'm having trouble:

I would like to calculate the fluctuation of a particular month vs the rate of the last month of previous qtr.

For example: If i'm selecting Aug in the slicer, i would like to get the trend from June (last month of Q2) to Aug (selected month).

another example - if selected month is May: I would like to see the fluctuation from March to May.

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @goldbergeddie,

In Dates table, please add a Quarter column. There is a one to many relationship between Dates table and M_Rate_Report.

Quarter = Dates[Date].[QuarterNo]

Suppose you already have Year column and Month column in M_Rate_Report table. Now, please add a Quarter column.

Quarter = LOOKUPVALUE(Dates[Quarter],Dates[Date],M_Rate_Report[Date])

Then, in M_Rate_Report table, create a two calculated columns which returns relative values of the last month of previous quarter.

Last R rte =
LOOKUPVALUE (
    M_Rate_Report[R rte],
    M_Rate_Report[Year], M_Rate_Report[Year],
    M_Rate_Report[Quarter], M_Rate_Report[Quarter] - 1,
    M_Rate_Report[Month], CALCULATE (
        MAX ( M_Rate_Report[Month] ),
        FILTER (
            M_Rate_Report,
            M_Rate_Report[Year] = EARLIER ( M_Rate_Report[Year] )
                && M_Rate_Report[Quarter]
                    = EARLIER ( M_Rate_Report[Quarter] ) - 1
        )
    )
)

Last conversion Factor =
LOOKUPVALUE (
    M_Rate_Report[Conversion Factor],
    M_Rate_Report[Year], M_Rate_Report[Year],
    M_Rate_Report[Quarter], M_Rate_Report[Quarter] - 1,
    M_Rate_Report[Month], CALCULATE (
        MAX ( M_Rate_Report[Month] ),
        FILTER (
            M_Rate_Report,
            M_Rate_Report[Year] = EARLIER ( M_Rate_Report[Year] )
                && M_Rate_Report[Quarter]
                    = EARLIER ( M_Rate_Report[Quarter] ) - 1
        )
    )
)

Then, create measure as below:

last month in last quarter =
SUMX (
    M_Rate_Report,
    M_Rate_Report[Last R rte] / M_Rate_Report[Last conversion Factor]
)

R Rate = SUMX(M_Rate_Report,M_Rate_Report[R rte]/[Conversion Factor])

Trend = CALCULATE([R Rate]/[last month in last quarter]-1)

Regards,
Yuliana Gu