Compare values to Custom Base Period

[AvGeek]

 Okay, so I have a model I need to build that involves a number of caveats.

1.  My financial data currently runs from April 2011 through December 2016, added monthly
   1. The data is provided in 20 different currencies (USD, CAD, JPY, GBP, etc.)
2. My financial year is from April through March of the following year (e.g. April 2015 through March 2016)
   1. I need to compare revenue on a monthly basis and yearly basis vs a specific Base Year period (October 2007 through September 2008)
   2. Ex. if I am looking at November 2015, I want to compare it to November 2007, but if I am looking at April 2015, I want to compare it to April 2008, even though April 2015 is earlier than November 2015
3. I need to compare to the FX-neutralized Base Year
   1. Ex. I need to compare November 2015 revenue converted to USD using November 2015 FX rates to November 2007 revenue using November 2015 FX rates

For this specific area of the model, lets say I have 4 tables (granularity and range in parentheses):

1. Calendar (Daily)
   1. 01-Oct-2007 through 31-Dec-2016
   2. continuous range
2. Currency (None, list of 20)
3. FX rates (Monthly)
   1. 01-Oct-2007 through 30-Sep-2008
   2. 01-Apr-2011 through 31-Dec-2016
   3. discontinuous range
4. RevNorm (Monthly)
   1. 01-Oct-2007 through 30-Sep-2008
   2. 01-Apr-2011 through 31-Dec-2016
   3. discontinuous range

I attached screenshots of the four tables to give an idea of the shape of the data (does this forum allow file upload?).

I have tried what seems like every combination of CALCULATE/FILTER/SUMX to get the values to calculate correctly, but it seems that I’m continuing to run into issues (incorrectly applied FX rates, incorrectly calculated subtotals, some combination of both, errors about multiple values supplied, etc.).

Ideally, for each month, I would like to calculate:

1. The sum of its revenues in local currency
2. The sum of its revenues converted to USD
3. The sum of its base year equivalent revenues in local currency
4. The sum of its base year equivalent revenues converted to USD (using current month’s FX rates)

Is there an easy solution I am overlooking here, like using some sort of time intelligence function?

Thanks!

CalendarCurrencyFXRevNormRelationships

[v-ljerr-msft]

Hi @AvGeek,

Could you try the formulas(untested) below to see if it works in your scenario?

1. 

The sum of its revenues in local currency = SUM ( RevNorm[AmountLocal] )

2.

The sum of its revenues converted to USD =
SUMX (
    RevNorm,
    RevNorm[AmountLocal]
        * LOOKUPVALUE (
            FX[FX_Rate],
            FX[Currency], RevNorm[Currency],
            FX[Date], RevNorm[Date]
        )
)

3.

The sum of its base year equivalent revenues in local currency =
VAR currentMonth =
    MAX ( Calendar[Month] )
RETURN
    IF (
        currentMonth >= 10,
        CALCULATE (
            SUM ( RevNorm[AmountLocal] ),
            FILTER (
                ALL ( Calendar ),
                Calendar[Year] = 2007
                    && Calendar[Month] = currentMonth
            )
        ),
        CALCULATE (
            SUM ( RevNorm[AmountLocal] ),
            FILTER (
                ALL ( Calendar ),
                Calendar[Year] = 2008
                    && Calendar[Month] = currentMonth
            )
        )
    )

4.

The sum of its base year equivalent revenues converted to USD =
VAR currentYear =
    MAX ( Calendar[Year] )
VAR currentMonth =
    MAX ( Calendar[Month] )
VAR currentDate =
    MAX ( Calendar[Date] )
RETURN
    IF (
        currentMonth >= 10,
        CALCULATE (
            SUMX (
                RevNorm,
                RevNorm[AmountLocal]
                    * LOOKUPVALUE (
                        FX[FX_Rate],
                        FX[Currency], RevNorm[Currency],
                        FX[Date], currentDate
                    )
            ),
            FILTER (
                ALL ( Calendar ),
                Calendar[Year] = 2007
                    && Calendar[Month] = currentMonth
            )
        ),
        CALCULATE (
            SUMX (
                RevNorm,
                RevNorm[AmountLocal]
                    * LOOKUPVALUE (
                        FX[FX_Rate],
                        FX[Currency], RevNorm[Currency],
                        FX[Date], currentDate
                    )
            ),
            FILTER (
                ALL ( Calendar ),
                Calendar[Year] = 2008
                    && Calendar[Month] = currentMonth
            )
        )
    )

Regards

[AvGeek]

Hi @v-ljerr-msft, 

Those formulas seemed to work with just a few exceptions:

1. The FX rates are divisors (that was an easy fix)
   
   
2. The subtotals for the base year calculations are incorrect. 

Any idea how to fix #2? 

 

Thanks!

[v-ljerr-msft]

Hi @AvGeek,

Could you try the formulas below to see if it works?

AmountLCL BY =
SUMX (
    Calendar,
    VAR currentMonth =
        MAX ( Calendar[Month] )
    RETURN
        IF (
            currentMonth >= 10,
            CALCULATE (
                SUM ( RevNorm[AmountLocal] ),
                FILTER (
                    ALL ( Calendar ),
                    Calendar[Year] = 2007
                        && Calendar[Month] = currentMonth
                )
            ),
            CALCULATE (
                SUM ( RevNorm[AmountLocal] ),
                FILTER (
                    ALL ( Calendar ),
                    Calendar[Year] = 2008
                        && Calendar[Month] = currentMonth
                )
            )
        )
)

AmountUSD BY =
SUMX (
    Calendar,
    VAR currentYear =
        MAX ( Calendar[Year] )
    VAR currentMonth =
        MAX ( Calendar[Month] )
    VAR currentDate =
        MAX ( Calendar[Date] )
    RETURN
        IF (
            currentMonth >= 10,
            CALCULATE (
                SUMX (
                    RevNorm,
                    RevNorm[AmountLocal]
                        / LOOKUPVALUE (
                            FX[FX_Rate],
                            FX[Currency], RevNorm[Currency],
                            FX[Date], currentDate
                        )
                ),
                FILTER (
                    ALL ( Calendar ),
                    Calendar[Year] = 2007
                        && Calendar[Month] = currentMonth
                )
            ),
            CALCULATE (
                SUMX (
                    RevNorm,
                    RevNorm[AmountLocal]
                        / LOOKUPVALUE (
                            FX[FX_Rate],
                            FX[Currency], RevNorm[Currency],
                            FX[Date], currentDate
                        )
                ),
                FILTER (
                    ALL ( Calendar ),
                    Calendar[Year] = 2008
                        && Calendar[Month] = currentMonth
                )
            )
        )
)

Regards