Adding rows to a "Same Period Last Year" calculation

[Anonymous]

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. 

With this said, I have two questions about this calculation: 

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. 

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

---

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.

Hope this can help your.

Best  Regards,

Cherry

[Anonymous]

This worked, thanks, Cherry!