Forum Discussion

AvGeek's avatar
AvGeek
Frequent Visitor
9 years ago

Compare values to Custom Base Period

 Okay, so I have a model I need to build that involves a number of caveats.      My financial data currently runs from April 2011 through December 2016, added monthly The data is provided in 20 ...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi AvGeek,

 

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

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's avatar
AvGeek
Frequent Visitor
9 years ago

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's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi AvGeek,

     

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

    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