[ Comparing clients across years ]

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