I'm doing a sales comparison report in which the end user can pick any 2 dates in a slicer (2016 and 2021 in below example).
To show the results, I'm trying to achieve this visual made with excel :
In PBI I used a table and a matrix to visualize the results but i can't replicate the excel table above.
I went first for a table for which I created 3 measures :
a) SalesTotal_YearOldest = CALCULATE([SalesTotal], FILTER(CALENDRIER, CALENDRIER[Year]=MIN(CALENDRIER[Year] ))) ----- to show 2016 results
b) SalesTotal_YearMostRecent = CALCULATE([SalesTotal], FILTER(CALENDRIER, CALENDRIER[Year]=MAX(CALENDRIER[Year] ))) ----- to show 2021 results
c) Variance = [SalesTotal_YearMostRecent]- [SalesTotal_YearOldest] / [SalesTotal_YearOldest]
Here's the visual :
This table is ok but i can't find a way to have dynamic column headers : YEAR(oldest) would be 2016 and YEAR(most recent) would be 2021 (based on the years picked in the slicer).
I've read many posts on dynamic column headers but it seems it's not possible for tables (unless you have an idea on how to do it ?).
So i opted for a matrix.
Here's the visual without the Variance measure :
Problem is i can't use the Variance measure in that matrix. The measure won't stay in the "Colonnes" field when i try to drag and drop the measure in. And when i put it in the "Valeurs" field, here's what it does :
Can anyone help me out reproducing the screenshot excel table please ?
Here's the data model if it can be of any help :
I hope my explanations are clear and detailed enough.