cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sunny9585
Helper I
Helper I

Sales Current Year vs Previous Year and YoY change

Hi all,

 

I need your help in building  a matrix in such a way that I need to show "Sales2019" and "Sales2020" in rows as values and their relative metrics as numbers and need to be done for many Measure such as "Units2019" and "Units2020" etc.

 

Please find the structure and the required output.

Structure of the Data:

DateProductRegionSales

Units

01/01/2019

AUS15410
02/01/2019BUS54221
01/01/2020AUS54852
02/01/2020BUS78358

 

Required Output:

MonthJanFeb
Sales2019154542

Sales2020

548783
YoY394241
Units20191021
Units20205258
YoY4237

 

Thanks in advance

 

5 REPLIES 5
AlB
Super User III
Super User III

Hi @Sunny9585 

1. Built a date table and create a relationship from it to Table1

2. Place DateT[Month] in columns of a matrix visual.

3. Create measures and place them in rows of the matrix visual. For instance:

Sales2019 = CALCULATE(SUM(Table1[Sales]), DateT[Year] = 2019)

 and so on for the other measures

You could also use DateT[Year] in the columns of the visual so that the years do not have to be hard-coded in the measures.  

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Hi @AlB ,

 

Thanks for your swift reply. Is there a way to make the measure act dynamically on th dste selection?

@Sunny9585 
Measure will dynamically change automatically, you can just create a year slicer. This measure will change based on your year selection.

 

Measure = SUM(Table1[Sales])

 

Paul Zheng _ Community Support Team

 

@Sunny9585 , dynamic you can make using time intelligence

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

or

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

hi @amitchandak ,

 

What about the measure names?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors