Sum for the date and for the same date in the previous year in the rows of a table

[ariaseze]

Hi Community,

I am trying unsuccessfully to show in the same row the value corresponding to the same date but from the previous year.

I tried different expressions of set analysis from the community but without success:

= SUM({< [SalesDate] = {$(=AddYears([SalesDate],-1))} >} [Price_ExtRetail])

= SUM({< [SalesDate] = {"$(=AddYears([SalesDate],-1))"} >} [Price_ExtRetail])

If someone can give me some light on this matter I really appreciate it.

Thanks!

[robert99]

H

Include this in your (calendar) script

AddMonths( SalesDate,-12) as PrevYrSalesDate , 

Then make your selections and do this in set analysis

SalesDate = P(PrevYrSalesDate) , MonthYear = , Year = , Month = etc

Take out all date selections made in the measure ie MonthYear etc

NB Leap yr 29/2 matched to 28/2

[treysmithdev]

Try this:

= SUM({< [SalesDate] = {"$(=AddYears([SalesDate],-1))"} >} [Price_ExtRetail])

[ariaseze]

Thank @treysmithdev for you reply, in my post I forgot the comma before -1 in the AddYears formula, but I had already dealt with the expression you indicate but without success.

[ariaseze]

@robert99, Can you tell me if I have any errors in my set analysis?

I tried what you said but I receive null for all rows..

=SUM({<SalesYear = , SalesMonth =, SalesDay = , SalesDate = {P(SalesDatePY)} >} Price_ExtRetail)

With these I receive 0 for all rows:

=SUM({< SalesDate = {"$(=P(SalesDatePY))"} >} Price_ExtRetail)

=SUM({< SalesYear = , SalesMonth =, SalesDay = , SalesDate = {"$(=P(SalesDatePY))"} >} Price_ExtRetail)

Thanks!

[robert99]

Hi

Try

SalesDate = P(SalesDatePY)

not

SalesDate = {P(SalesDatePY)}

The outer brackets should be removed

NB If this doesn't work check using a table and dimensions of SalesDate and SalesDatePY to ensure each SalesDate has the correct previous years date

[ariaseze]

@robert99, same result 😞
I decided to use the calendar with flags to solve it, but logically I had to add the flag to all my calculations so that the values are not disturbed.

Thanks both of you for your collaborations. @robert99  and @treysmithdev .