Forum Discussion
Helper IVTime-Intelligence: YTD / SamePeriodLastYear -- Odd behavior
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:
- I have a line graph with 3 measures (seen below) and x-axis as Months:
- 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
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] ) )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().
try to set your relationship to the Date table from Both to Single
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.
9 Replies
- greggyb
Resident Rockstar
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] ) )
cwayne758Perfect! 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:
- 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.
- I have a series of notecards organized into columns.
- Column 1 = Current Year Metrics (2016)
- Column 2 = Prior Year Metrics (example of one of the metrics below)
Move Count PYTD = CALCULATE(COUNTA(DimFinancialsGMMS[Moves ]), SAMEPERIODLASTYEAR(DimDateAccrued[DateKey Accrued]))
- Page Slicer with Years.
- 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.
- andre
Memorable Member
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().