Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
goldbergeddie
New Member

Show fluctuation Qtr end until today

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?

2 ACCEPTED SOLUTIONS

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
        )
    )
)

5.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

 

 image.png

 

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!

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
        )
    )
)

5.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.