Forum Discussion
Advocate IINeed help with measure! Chart with previous year values based on current-year slicer
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/RevenueTYTo 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?
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?
2 Replies
- MarkLaf
Super User
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?
- Ashish_Mathur
Hi,
If,
- There is a Date column in Table1; and
- There is a Calendar Table; and
- There is a relationship from the Date column of Table1 with the Date column of the Calendar Table
- In the Calendar Table, you have extracted the Year from the Date by using this calculated column formula = YEAR(Calendar[Date])
- 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.
