[ Comparing clients across years ]

[bolabuga]

Hello everyone,

I would like help with the calculation described on the screenshot:

theres a sample table in excel here: https://drive.google.com/open?id=0B8Aq8DhGApJqUXJLWDJqekxvaWs

Thks in advance

[v-yulgu-msft · Accepted solution]

Hi @bolabuga,

Based on currect sample data, suppose that the data type of column [MONTHYEAR] is set to text. 

Then, we should add some calculated columns into this table:

Year = RIGHT(Revenue[MonthYear],2)
Mon = LEFT(Revenue[MonthYear],3)

Clinet exist in another year =
IF (
    Revenue[Year] = 16,
    LOOKUPVALUE (
        Revenue[Clients],
        Revenue[Year], Revenue[Year] + 1,
        Revenue[Mon], Revenue[Mon],
        Revenue[Clients], Revenue[Clients]
    ),
    LOOKUPVALUE (
        Revenue[Clients],
        Revenue[Year], Revenue[Year] - 1,
        Revenue[Mon], Revenue[Mon],
        Revenue[Clients], Revenue[Clients]
    )
)

REVENUE2 =
IF (
    Revenue[Clinet exist in another year] <> BLANK (),
    CALCULATE (
        SUM ( Revenue[REVENUE] ),
        ALLEXCEPT ( Revenue, Revenue[Clients], Revenue[MonthYear] )
    ),
    BLANK ()
)

LY REVENUE =
LOOKUPVALUE (
    Revenue[REVENUE2],
    Revenue[Clients], Revenue[Clients],
    Revenue[Year], Revenue[Year] - 1,
    Revenue[Mon], Revenue[Mon]
)

The data table will be like:

 

Based on my understanding, you only want to display revenue for 2017 in visual, right? I am not sure what visualization you want to use, in my test, I added a table visual to show data.

Use below formula to create a table which only contains data for 2017.

Table =
CALCULATETABLE ( Revenue, Revenue[Year] = 17 )

Best regards,
Yuliana Gu

[bolabuga]

@v-yulgu-msft

Really thanks, 

I can apply this solution and on the top of that it cleaned some doubts i had on using lookupvalue function.