Table with year and deltas

[zervino]

I have raw data with this format:

Team	Year	Sales
A	2021	6345
B	2021	4554
C	2021	8525
A	2022	7000
B	2022	4634
C	2022	7122
A	2023	8788
B	2023	3437
C	2023	5998

I have data for many different teams and many different years.

The output should be a table like this:

Team	2023	2022	Delta
A	8788	7000	1788
B	3437	4634	-1197
C	5998	7122	-1124

I have a drop down in the dashboard where I can select the two years to compare, but the output will still show the results for all teams.

What's the best way to achieve this kind of table?

[Ritaf1983 · Accepted solution]

Hi @zervino 

You can apply these steps :
1. Create the table for Years :

Years = DISTINCT('Table'[Year])

2. create a relationship with your table:

3. Create 3 measures :

Max Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=max('Years'[Year]) )

Min Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=min('Years'[Year]) )

Delta = [Max Year]-[Min Year]

4. create a visual:

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @zervino 

You can apply these steps :
1. Create the table for Years :

Years = DISTINCT('Table'[Year])

2. create a relationship with your table:

3. Create 3 measures :

Max Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=max('Years'[Year]) )

Min Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=min('Years'[Year]) )

Delta = [Max Year]-[Min Year]

4. create a visual:

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[zervino]

The problem with this approach is that the column title is not dynamic, it would be "max year", but depending on which years the user chooses, I would like to see eg. 2022 and 2016

[v-jingzhan-msft · Accepted solution]

Hi @zervino 

You can create a measure like below. Use a matrix visual instead of a table visual. Add Team to rows, Add Year to column and add the measure to Values. Rename the column total from the default "Total" to "Delta". 

Value = IF(ISINSCOPE(Years[Year]),SUM('Table'[Sales]),[Delta])

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Ritaf1983]

@zervino 

For dynamic measure names you can use a workaround with field parameters .
Please refer to the linked video:

https://www.youtube.com/watch?v=9_2m5Csr55c

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[ajohnso2]

1. Add a dimension table of your years

2. Add a dimension table of your teams

3. Join years & team table to your 'Fact'

4. Use dax to calculate a new measure for LY