Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
zervino
Helper I
Helper I

Table with year and deltas

I have raw data with this format:

 

TeamYearSales
A20216345
B20214554
C20218525
A20227000
B20224634

C

20227122

A

20238788

B

20233437

C

20235998

 

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

 

The output should be a table like this:

 

Team20232022Delta
A878870001788
B34374634-1197
C59987122-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
Ritaf1983
Super User
Super User

Hi @zervino 

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

Years = DISTINCT('Table'[Year])
Ritaf1983_0-1715362404041.png

2. create a relationship with your table:

Ritaf1983_1-1715362465714.png

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:
Ritaf1983_2-1715362947225.png

pbix is attached

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

View solution in original post

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])

vjingzhanmsft_0-1715569173531.pngvjingzhanmsft_2-1715569229173.png

 

vjingzhanmsft_1-1715569192418.png

 

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

 

View solution in original post

5 REPLIES 5
Ritaf1983
Super User
Super User

Hi @zervino 

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

Years = DISTINCT('Table'[Year])
Ritaf1983_0-1715362404041.png

2. create a relationship with your table:

Ritaf1983_1-1715362465714.png

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:
Ritaf1983_2-1715362947225.png

pbix is attached

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

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

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])

vjingzhanmsft_0-1715569173531.pngvjingzhanmsft_2-1715569229173.png

 

vjingzhanmsft_1-1715569192418.png

 

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

 

@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
Advocate I
Advocate I

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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