Forum Discussion
Helper VMetric to use on a List(table?) Chart.
In your scenario, you also have client column in your table, you should also add "Table[Client]=EARLIER(Table[Client])" in your filter.
Previous Year Value = CALCULATE(SUM(Table[Value]),FILTER(Table,Table[Year]=EARLIER(Table[Year])-1 && Table[Month]=EARLIER(Table[Month]) && Table[Client]=EARLIER(Table[Client])))
Since you need to do this comparison in matrix, it will generate columns dynamically and always aggregate on Values cell, I can't find a better way using a "previous year data" column.
Regards,
Simon Hou
Microsoft Employee
Based on your source table, it's better to use a matrix instead of a table visual to render the data.
In your scenario, your calculation need to use Year 2015 calculation divide by Year 2016 calculation. We can add a column in your table which returns previous year data.
Previous Year Value = CALCULATE(SUM(Table3[Value]),FILTER(Table3,Table3[Year]=EARLIER(Table3[Year])-1 && Table3[Month]=EARLIER(Table3[Month])))
Then we can create a "Current Year/Previous Year" measure and add it into the matrix.
Current / Previous = IF(CALCULATE(SUM(Table3[Previous Year Value]))=BLANK(),0,CALCULATE(SUM(Table3[Value]))/CALCULATE(SUM(Table3[Previous Year Value])))
'
Regards,
bolabugasimon hou-msft thks for trying to help :).
The idea seens very good, but its not working because the calculated column repeat the sum for every client each mont.
showing the sample data.
CLIENT MONTH YEAR MONEY TYPE 3 1 2015 50 0 7 1 2015 97 1 5 1 2015 25 2 3 1 2016 337 0 7 1 2016 277 1 5 1 2016 555 2 3 2 2015 150 0 7 2 2015 137 1 5 2 2015 110 2 3 2 2016 127 0 7 2 2016 156 1 5 2 2016 322 2 3 3 2015 120 0 7 3 2015 667 1 5 3 2015 160 2 3 3 2016 44 0 7 3 2016 145 1 5 3 2016 80 2
The result showing the replicated results on the calculated column.
It should have a way to do this without repeating the sum for every different client in the calculated column.
- v-sihou-msft
In your scenario, you also have client column in your table, you should also add "Table[Client]=EARLIER(Table[Client])" in your filter.
Previous Year Value = CALCULATE(SUM(Table[Value]),FILTER(Table,Table[Year]=EARLIER(Table[Year])-1 && Table[Month]=EARLIER(Table[Month]) && Table[Client]=EARLIER(Table[Client])))
Since you need to do this comparison in matrix, it will generate columns dynamically and always aggregate on Values cell, I can't find a better way using a "previous year data" column.
Regards,
Simon Hou
- bolabuga
Worked Simon_Hou-MSFT, really thks, it is a much better way to do, because i can use just one metric across all years. Really cool. thkk again :smileylol: