Forum Discussion

Clara's avatar
Clara
Icon for Advocate II rankAdvocate II
7 years ago
Solved

Need 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:   Profitabi...
Need Help
  • MarkLaf's avatar
    MarkLaf
    7 years ago

    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:

    YearValueTypeValue
    2010Profit10
    2010Revenue100
    2011Profit20
    2011Revenue100
    2012Profit30
    2012Revenue90
    2013Profit100
    2013Revenue200
    2014Profit200
    2014Revenue300
    2015Profit150
    2015Revenue350

     

     

    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?

     

MarkLaf's avatar
MarkLaf
Icon for Super User rankSuper User
7 years ago

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:

YearValueTypeValue
2010Profit10
2010Revenue100
2011Profit20
2011Revenue100
2012Profit30
2012Revenue90
2013Profit100
2013Revenue200
2014Profit200
2014Revenue300
2015Profit150
2015Revenue350

 

 

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?