Need help with measure! Chart with previous year values based on current-year slicer

[Clara]

So, my report has two slicers: one for years and one for (store) units. Both allow for multiple selections. So far I've managed to obtain each year's profitability through this measure:

ProfitabilityTY = 

VAR ProfitTY = CALCULATE(SUM('Table1'[Value]);ALLSELECTED('Table1'[Year]);ALLSELECTED('Table1'[Unit]);'Table1'[ValueType]="Profit")

VAR RevenueTY = CALCULATE(SUM('Table1'[Value]);ALLSELECTED('Table1'[Year]);ALLSELECTED('Table1'[Unit]);'Table1'[ValueType]="Revenue")

RETURN ProfitTY/RevenueTY

To use in a chart visual.

But now I want to know the difference (growth) between each year's profitability value and the previous one's. In summary, I want to make another measure ("ProfitabilityLY", as in "last year") and a third measure which compares the two (Growth = ProfitabilityTY-ProfitabilityLY).

I've tried ALLSELECTED('Table1'[Year])-1 (I'm a total noob at this PBI thing) but obviously, that didn't work. Is there any obvious way I haven't thought of?

[Ashish_Mathur]

Hi,

If,

1. There is a Date column in Table1; and
2. There is a Calendar Table; and
3. There is a relationship from the Date column of Table1 with the Date column of the Calendar Table
4. In the Calendar Table, you have extracted the Year from the Date by using this calculated column formula = YEAR(Calendar[Date])
5. In your visual, you have dragged the Year from the Calendar Table

then, these simple measures will work

Total Profit = CALCULATE(SUM('Table1'[Value]);'Table1'[ValueType]="Profit")

Total Revenue = CALCULATE(SUM('Table1'[Value]);'Table1'[ValueType]="Revenue")

Profit % = [Total Profit]/[Total Revenue]

Profit % in PY = CALCULATE([Profit %],PREVIOUSYEAR(Calendar[Date]))

Hope this helps.

[MarkLaf · Accepted solution]

You shouldn't need those ALLSELECTED filters I think, so I rewrote your ProfitabilityTY as well as providing the others. The following worked with this test data, the structure of which I guessed at from your measures:

Year	ValueType	Value
2010	Profit	10
2010	Revenue	100
2011	Profit	20
2011	Revenue	100
2012	Profit	30
2012	Revenue	90
2013	Profit	100
2013	Revenue	200
2014	Profit	200
2014	Revenue	300
2015	Profit	150
2015	Revenue	350

ProfitabilityTY = 
VAR ProfitTY = CALCULATE(SUM(Table1[Value]),Table1[ValueType]="Profit")
VAR RevenueTY = CALCULATE(SUM(Table1[Value]),Table1[ValueType]="Revenue")
RETURN
DIVIDE(ProfitTY,RevenueTY)

ProfitabilityLT = 
CALCULATE(
    [ProfitabilityTY],
    FILTER(
        ALL(Table1[Year]),
        Table1[Year]=SELECTEDVALUE(Table1[Year])-1
    )
)

Growth = 
IF(
    //checks if this is first year, i.e. no data in LT, can also do isblank check on [ProfitabilityLT]
    SELECTEDVALUE(Table1[Year])=CALCULATE(MIN(Table1[Year]),ALL(Table1[Year])),
    BLANK(),
    [ProfitabilityTY]-[ProfitabilityLT]
)

Table visual with years and measures:

Does that help?