cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Table with year and deltas

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?

2 ACCEPTED SOLUTIONS
Super User

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.

Community Support

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!

5 REPLIES 5
Super User

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.

Helper I

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

Community Support

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!

Super User

For dynamic measure names you can use a workaround with field parameters .

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

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

4. Use dax to calculate a new measure for LY

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors