SAMEPERIODLASTYEAR in a Calculated Column

[Anonymous]

Hi,

I need the Sales Last Year (Sales LY) as a Calculated Column in my Data Base so I can compare Sales vs Sales LY in a Scatter Chart by Peer.

My problem is that when I apply the SAMEPERIODLASTYEAR formula I get the same Value as this year rather than LY . 

In the below Value LY = 

CALCULATE ( SUM(Append1[Value]),

FILTER(ALL(Append1[Date]),

SAMEPERIODLASTYEAR(Append1[Date]))) ... but I get 1100 instead of 1045 from last year:

If I use 

In the below Value LY = 

CALCULATE ( SUM(Append1[Value]),

SAMEPERIODLASTYEAR(Append1[Date])) ... It come a Blank cell

For sure I am not getting right the filters or orw context ... can someone please help me?

Thanks

[Tahreem24]

You could also try the below :

CALCULATE ( SUM(Append1[Value]),

DATEADD(Append1[Date], -1,YEAR))

or

CALCULATE ( SUM(Append1[Value]),

ParallelPeriod(Append1[Date], -1,YEAR))

[mahoneypat]

In your measure, you are trying to get the Sum for the date one year ago.  If that is what you are looking for (vs. getting a sum over a broader previous period like quarter), this expression would do it too:

Sales LY =
VAR ayearago =
DATE ( YEAR ( Append1[Date]) - 1, MONTH ( Append1[Date] ), DAY ( Append1[Date] ) )
RETURN
CALCULATE (
SUM ( Append1[Value] ),
ALL ( Append1 ),
Append1[Date] = ayearago,
VALUES ( Append1[Peer] )  
)

I am not sure what columns you need to keep constant (Peer, Source, etc.) so you can add/replace a Values() term like this for each one to keep the same for the calculation.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jkball04]

This was the only resolution from this page that resolved my issue.

[Anonymous]

Thanks for your answer, I have tried the below but is still not giving me the Last Year Value for the Account Selected.

Value LY =

VAR ayearago =

DATE ( YEAR ( Append1[Date]) - 1, MONTH ( Append1[Date] ), DAY ( Append1[Date] ) )

RETURN

CALCULATE (

SUM ( Append1[Value] ),

ALL ( Append1 ),

Append1[Date] = ayearago,

VALUES ( Append1[Peer] ), VALUES(Append1[Cons Lvl]), VALUES(Append1[Period View]), VALUES(Append1[Account]), VALUES(Append1[Region]),VALUES(Append1[Source Date]), VALUES(Append1[Source])

)

[Anonymous]

Just for clarification, My Period View includes only 3 dimensions QTD, YTD and LTM. YTD and LTM are not necessarilly the sum of the Quarters since due to rounding several companies does not make that precise. So the data base has all 3 of them as a Value for each Peer.

[amitchandak]

@Anonymous , Make sure you use Date calendar for that

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))
// Forced from Today
YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
//
LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)
//Forced from Max date this year
YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


//

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...