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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

Hi @Anonymous 

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

Anonymous
Not applicable

Hi @AlB ,

 

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

@Anonymous 
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

 

@Anonymous , 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.

 

 

Anonymous
Not applicable

hi @amitchandak ,

 

What about the measure names?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.