Time-Intelligence: YTD / SamePeriodLastYear -- Odd behavior

[cwayne758]

Hi, beginner question here but here goes!

I am trying to use the time-intelligence functions in order to avoid hard-coding fixed dates into my formulas.

Context:

1.  I have a line graph with 3 measures (seen below) and x-axis as Months:
2. Page slicer set to "Year 2016"

YTD Revenue = TOTALYTD(SUM(FactFinancialsGP[Revenue]), DimDateAccrued[DateKey Accrued])

Revenue PYTD = CALCULATE([YTD Revenue], SAMEPERIODLASTYEAR(DimDateAccrued[DateKey Accrued]))

2015 Revenue = CALCULATE(SUM(FactFinancialsGP[Revenue]), DimDateAccrued[Year Accrued] = 2015)

However, Revenue PYTD behaves wrong. It is cummalitively summing the revenue month-over-month, instead of plotting the revenue for each month respectively. 

2015 Revenue measure plots it correctly, as seen in the photo.

green line is behaving strange

Question: Why does SamePeriodLastYear behave this way versus the hard-coded measure? 

@greggyb perhaps you could enlighten me 🙂 

Thank you everyone in advance

[greggyb · Accepted solution]

You're getting a YTD total with SAMEPERIODLASTYEAR() because the expression that you are evaluating is a YTD measure. Use the DAX below to have a measure that will automatically mirror the current context, just shifted one year back.

=CALCULATE(
    SUM( FactFinancialsGP[Revenue] )
    ,SAMEPERIODLASTYEAR( DimDateAccrued[DateKey Accrued] )
)

[andre · Accepted solution]

your Revenue PYTD measure works as expected.  You only have data for one month in the current year, therefore your YTD for current year is parallel to X, however, you probably have data for every month of the prior yer, therefore, your YTD for Prior year sums all prior periods for each period, which is how the YTD calc is supposed to work.  If you only want to show what the revenue was in that period, don't user TOTALYTD function, just use the Sum().

[andre · Accepted solution]

try to set your relationship to the Date table from Both to Single

[andre · Accepted solution]

your Revenue PYTD measure works as expected.  You only have data for one month in the current year, therefore your YTD for current year is parallel to X, however, you probably have data for every month of the prior yer, therefore, your YTD for Prior year sums all prior periods for each period, which is how the YTD calc is supposed to work.  If you only want to show what the revenue was in that period, don't user TOTALYTD function, just use the Sum().

[greggyb · Accepted solution]

You're getting a YTD total with SAMEPERIODLASTYEAR() because the expression that you are evaluating is a YTD measure. Use the DAX below to have a measure that will automatically mirror the current context, just shifted one year back.

=CALCULATE(
    SUM( FactFinancialsGP[Revenue] )
    ,SAMEPERIODLASTYEAR( DimDateAccrued[DateKey Accrued] )
)

[cwayne758]

Perfect! Very clear explanation. Thank you sir

[cwayne758]

Hello, 

So I have another question about the contiguous date requirements of SamePeriodLastYear function.

Let me describe my setup to help illustrate what I am trying to do. 

Setup:

1. I have a stacked bar chart with Clients and Gross Revenue. It is being used to cross-filter the rest of the visuals on the page.
2. I have a series of notecards organized into columns. 
   1. Column 1 = Current Year Metrics (2016)
   2. Column 2 = Prior Year Metrics (example of one of the metrics below)

      Move Count PYTD = CALCULATE(COUNTA(DimFinancialsGMMS[Moves ]), SAMEPERIODLASTYEAR(DimDateAccrued[DateKey Accrued]))

3. Page Slicer with Years.
4. I am using a role playing Date Dimension table in my datamodel with no missing dates (using DateStream)

Problem: 

• When I select a Client from the stacked bar chart, all my Prior Year metrics break and I get the message: "SamePeriodLastYear only works with contiguous dates."
• With no individual Client selected, all metrics appear correctly. It is during the cross-filtering scenario that I encounter issues. 

Any suggestions @greggyb @andre? Thank you. 

[greggyb]

Is there any way you can share a sample .pbix file that reproduces the issue?

[cwayne758]

Unfortunately I cannot share this particular .pbix file. 

Here are some photos (Ignore Growth column):

No clients selected from bar chart, 2016 selected from Year slicerWith a client selected from bar chart, and 2016 selectedrelevant part of model underlined in purple

[greggyb · Accepted solution]

I can reproduce. You've got bidirectional relationships in the chain from DimFinancialsGMMS to DimDateAccrued.

I'm assuming [Client] exists in DimFinancialsGMMS. When you click on a client in the chart, that filter flows all the way through to DimDateAccrued. If you select a client that does not have rows in FactMoves for every single date in the two years we're considering, then DimDateAccrued is filtered down to a less than contiguous range of dates.

Change your relationship between DimDateAccrued and FactMoves into a one-way relationship.

[andre · Accepted solution]

try to set your relationship to the Date table from Both to Single

[cwayne758]

dude...YES!!! Thanks a million.