Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

SAMEPERIODLASTYEAR in a Calculated Column

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 SAMEPERIODLASTYEA...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago
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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.