cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clangelica Frequent Visitor
Frequent 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. 

 

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Adding rows to a "Same Period Last Year" calculation

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.

Capture.PNG

Hope this can help your.

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.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Adding rows to a "Same Period Last Year" calculation

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.

Capture.PNG

Hope this can help your.

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.

View solution in original post

Highlighted
clangelica Frequent Visitor
Frequent Visitor

Re: Adding rows to a "Same Period Last Year" calculation

This worked, thanks, Cherry! 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 62 members 954 guests
Please welcome our newest community members: