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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Help :(Need to do YOY calculation, however do I need a date table to do this, is there a workaround?

I am very very new to PowerBI and I'm still getting my head around it, please pardon my ignorance

 

I have a data set

 

    Shoe Type | FY13 FY14 FY15 FY16 | Total 

      Heels         20     30     10     50      110

      Flats            5      10     20     15       50

      Trainers      50     50     70    100    270

 

I would like to show YOY percentage growth i.e. growth from FY15 to FY16 FOR "Heels" or from FY14 TO FY15 for "trainers". My companies financial year runs from June to June. Is there a way to have the YOY calculation, so that it updates with slicer selection, however without using a calendar table? I have been trying without success to create a calendar table and I was wondering if there is a simpler way to do this and have this automatically update for selections without creating a measure for each shoe type and for each FY year. 

 

Thanks in advance 

 

Pangea 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@Anonymous

 

In your scenario, it looks like your are using a matrix to render your data. Since you always need to compare the sales with previous year, you can create a calculated column for previous year data. I suggest you use a numeric column to store fiscal year instead of "FY xx".

 

Previous Year Sales = 
CALCULATE(
SUM(Table[Sales]),
FILTER(Table,
Table[Fiscal Year]=EARLIER(Table[Fiscal Year])-1 && Table[Shoe Types]=EARLIER(Table[Shoe Types])
)
)

Then you can create your YOY calculation based on above column.

 

 

Growth = CALCULATE(SUM(Table[Sales])-SUM(Table[Previous Year Sales]))/CALCULATE(SUM(Table[Previous Year Sales]))

 

If you apply slicer for Shoe Types, this measure can be sliced with your selection as well.

 

Regards,

Simon Hou

 

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@Anonymous

 

In your scenario, it looks like your are using a matrix to render your data. Since you always need to compare the sales with previous year, you can create a calculated column for previous year data. I suggest you use a numeric column to store fiscal year instead of "FY xx".

 

Previous Year Sales = 
CALCULATE(
SUM(Table[Sales]),
FILTER(Table,
Table[Fiscal Year]=EARLIER(Table[Fiscal Year])-1 && Table[Shoe Types]=EARLIER(Table[Shoe Types])
)
)

Then you can create your YOY calculation based on above column.

 

 

Growth = CALCULATE(SUM(Table[Sales])-SUM(Table[Previous Year Sales]))/CALCULATE(SUM(Table[Previous Year Sales]))

 

If you apply slicer for Shoe Types, this measure can be sliced with your selection as well.

 

Regards,

Simon Hou

 

Anonymous
Not applicable

Thank you Simon, really appreciate the help. 

 

Smiley Happy

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.