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
hbolo
Regular Visitor

YoY YTD Revenue Trends -- Should not Include Entire Month

I am analyzing Year-over-Year, Year-to-Date revenue trends and I'm having some troubles coming up with the right formula for the Prior Year Amounts. For example, it's now January 2, so I want to compare Jan 1-2 2018 revenue with Jan 1-2 2019 revenue.

 

When I have used SAMEPERIODLASTYEAR the formula returns the entire Jan 2018 amount instead of just the first and second days of the month. I have tried a couple more complex equations that I found on the forums that involved naming variables and if statements, but I ran into the same issue with each permutation I tried. Is there a solution for this?

 

Formula: 

2018 YTD Revenue = CALCULATE(Revenue[2018 Revenue],SAMEPERIODLASTYEAR('Date Table'[Date]))

 

Worksheet Setup: 

  • Two tables -- one is a date table ("Date Table") and one has revenue info ("Revenue")
  • Revenue Amounts span from Jan 1, 2018 - Jan 16, 2019

Dropbox link to Download: Link

 

Thanks in advance for your help! Please let me know if you need any more info...

1 ACCEPTED SOLUTION

@hbolo

 

If you place the dates (up to the day level) in the rows of the matrix, just like you had in your pbix, it should work. The measures will return the YTD up to the day in the current row.

View solution in original post

10 REPLIES 10

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.