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

Need to compare YOY sales in a table without a specific date

I have a table with sales by sales rep by month.  This table does not include an actual date.  Instead it has month name, month number, and Fiscal year in the format FYxx (We operate on a Sep-Aug fiscal year).  I am trying to put together an output that will show sales among other data with Fiscal years across the top and sales reps down the side. 

 

I need help creating a YOY Sales Growth measure that compares FYXX to FYXX-1.  For instance, I need the FY20 column YOY Sales Growth to compare to data in my table with a Fiscal year of "FY19" and so on.  Any help would be appreciated.  The column currently in the report is calculating the same value for every fiscal year because they hardcoded the FYs into the measure.

 

Data TableData TableDesired Output (Column not currently Calculating correctly)Desired Output (Column not currently Calculating correctly)

1 REPLY 1
VijayP
Super User
Super User

First you need to create date Dimension file and relate that table with Fact Table (i..e, your data file).
To get the actual date in a single column you can concatenate the Date,Month and Year and then change type to DAte.
as shown in this snapshot.

Next you need to create two measure 
1. SUm(Total Cost)

2.LY = Calculate([Total Cost], SAMEPERIODLASTYEAR(DateDim[Date])

3. If you want you can create the Difference between these by subtracting LY from total Cost

4. you need to keep a Year Slicer from DateDim table

you can drag both measure side by side in a table Visual and you can find the comparison

IF still need clarity please follow this video by clicking here

Also you can watch my YouTube Videos on Power Bi and Excel from www.youtube.com/perepavijay

 

If find this as solution please mention as solution and share your Kudoes

Regards

Vijay Perepa

 

 

2020-04-28_233756.png




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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.