Forum Discussion
SAMEPERIODLASTYEAR in a Calculated Column
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
- 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] ) )RETURNCALCULATE (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]))
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.
- This was the only resolution from this page that resolved my issue.