Reply
Frequent Visitor
Posts: 5
Registered: ‎01-02-2019
Accepted Solution

YoY YTD Revenue Trends -- Should not Include Entire Month

[ Edited ]

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...


Accepted Solutions
AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

@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


All Replies
AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

Hi @hbolo

How about you try these two measures in the matrix visual you already have? 

 

YTDRevenue = 
CALCULATE(
SUM(Revenue[Revenue]);
DATESYTD('Date Table'[Date])
)
PreviousYearYTDRevenue =
CALCULATE (
    SUM ( Revenue[Revenue] );
    DATESYTD ( SAMEPERIODLASTYEAR ( 'Date Table'[Date] ) )
)
 
Frequent Visitor
Posts: 5
Registered: ‎01-02-2019

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

This PreviousYearYTDRevenue formula here returns the entire January 2018 amount for me -- I only want the first days of January. Were you able to get it to work?

AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

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

Frequent Visitor
Posts: 5
Registered: ‎01-02-2019

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

@AlBThat worked! 

 

Does that date field always need to be on the rows/columns for the measure to behave in this way? For example, is there a way for me to use this measure on the Card or Gauge visualization without showing the entire PY month's revenue amounts?

AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

@hbolo

 

The measures will take the latest date in filter context as base. If filter context is absent, they will take the latest date in the Date table. You can set them in a Card with a slicer to determine what date you want in the filter context.

Frequent Visitor
Posts: 5
Registered: ‎01-02-2019

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

@AlB

 

Thanks! Any tips to do this slicer in an automated fashion?

AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

@hbolo

What do you mean exactly?

Frequent Visitor
Posts: 5
Registered: ‎01-02-2019

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

@AlB

 

Effectively make the slicer automatically update based on today's date. For example, as time progresses the slicer forward like the TODAY() function would. For a YoY comparison it'd be extremely helpful if there was some way to do TODAY() - 1 year.

 

Does that make sense?

Member
Posts: 72
Registered: ‎06-24-2015

Re: YoY YTD Revenue Trends -- Should not Include Entire Month

You can find several examples in this article: https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/