cancel
Showing results for
Did you mean:
Regular Visitor

## Adding rows to a "Same Period Last Year" calculation

I have a data set that lists sales for specific dates from 2017 through 2019.  I created the following calculation for me to compare sales on a year-over-year basis:

Prior Year Sales = CALCULATE(SUM('Margin Query'[SALES]),SAMEPERIODLASTYEAR('Margin Query'[YOY_DATE]),ALL('Margin Query'))

The calculation works properly when I am comparing sales using the "YoY Date" field as my only row.  This view is generated using a matrix:

Month End Date              Sales     Prior Year Sales

2/28/2017          \$15,000,000      -

2/28/2018          \$20,000,000      \$15,000,000

I would like to add additional rows to the view above so I can see how individual groups performed on a year-over-year basis.  As such, I added a data attribute called "Group" to break out sales by group.  When I do this, however, I am noticing that the "Prior Year Sales" rows for 2/28/2018 lists the total sales for 2/28/2017, rather than the sales for each individual group.  Here is an example of what I am seeing:

Month End Date              Sales     Prior Year Sales

2/28/2017

Group A              \$5,000,000         -

Group B              \$5,000,000         -

Group C              \$3,000,000         -

Group D              \$2,000,000         -

2/28/2018

Group A              \$6,000,000         \$15,000,000

Group B              \$6,000,000         \$15,000,000

Group C              \$5,000,000         \$15,000,000

Group D              \$3,000,000         \$15,000,000

Instead of seeing "\$15,000,000" listed above in the "Prior Year Sales" column, I would like to see the sales for each comparable group listed.

1)  Do I need to invoke the "Group" field in my calculation above in order for it to pivot properly?

2)  If the answer to (1) is "Yes," is it possible to invoke 2+ fields in this calculation?  Let's say that I want to pivot by "Group" and by "State."

Thanks for the feedback.

1 ACCEPTED SOLUTION
Resident Rockstar

Hi @clangelica ,

1)  Do I need to invoke the "Group" field in my calculation above in order for it to pivot properly?

2)  If the answer to (1) is "Yes," is it possible to invoke 2+ fields in this calculation?  Let's say that I want to pivot by "Group" and by "State."

1) Yest.

2) We could invoke 2+fields in that calculation.

Please refer to my test sample. Here is the measure formula.

`Measure = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group],'Table'[status]),SAMEPERIODLASTYEAR('Table'[Date]))`

Here is the output.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Resident Rockstar

Hi @clangelica ,

1)  Do I need to invoke the "Group" field in my calculation above in order for it to pivot properly?

2)  If the answer to (1) is "Yes," is it possible to invoke 2+ fields in this calculation?  Let's say that I want to pivot by "Group" and by "State."

1) Yest.

2) We could invoke 2+fields in that calculation.

Please refer to my test sample. Here is the measure formula.

`Measure = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group],'Table'[status]),SAMEPERIODLASTYEAR('Table'[Date]))`

Here is the output.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

This worked, thanks, Cherry!

Announcements