cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erosales
Frequent Visitor

How can I compare if Prior Period Sales Increased, but for a row element

Hi, I'm trying to create a column in Power BI that can quickly indicate whether Account Sales increased compared to previous quarter. The formula I created computes for the whole column and I'm not able to get the row-specific comparison.

 

QuarterAccountSalesDid Sales Increase vs Prev Month?
Q1ABC 1,000 
Q2ABC    600No
Q3ABC    800Yes
Q4ABC    900Yes
Q1XYZ    300 
Q2XYZ    400Yes
Q3XYZ      90No
Q4XYZ    200Yes

 

Any help will be greatly appreciated.

 

Thanks!

1 ACCEPTED SOLUTION
negi007
Super User
Super User

@erosales you can modify your measure like below

 

did_sales_increase =
var curr = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],0,QUARTER))
var prv = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],-1,QUARTER))
return
if ( curr > prv, "Yes", "No")
 
sales_chg =
var curr = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],0,QUARTER))
var prv = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],-1,QUARTER))
return
curr - prv
 
negi007_0-1638452165363.png

 

 

i have also attached pbix file for your reference.




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


View solution in original post

5 REPLIES 5
negi007
Super User
Super User

@erosales you can modify your measure like below

 

did_sales_increase =
var curr = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],0,QUARTER))
var prv = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],-1,QUARTER))
return
if ( curr > prv, "Yes", "No")
 
sales_chg =
var curr = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],0,QUARTER))
var prv = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date].[Date],-1,QUARTER))
return
curr - prv
 
negi007_0-1638452165363.png

 

 

i have also attached pbix file for your reference.




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


View solution in original post

erosales
Frequent Visitor

Sorry, turns out your formula is enough. I wasn't aware of the differences between measures and calculated columns earlier. Your suggestion worked like a charm when I used it with my filters. Thanks again for your help!

erosales
Frequent Visitor

Hi, yes I see that it works as a Measure. But I was hoping to use the formula to add a new column in the Data table as I plan to use it as an additional filter for the data that I want to see. Like take into account only the accounts with sales increases for each particular period.

 

Sorry, I'm very new to this so I'm not too familiar with what is and what isn't allowed by the tool.

 

Thanks so much for your help!

negi007
Super User
Super User

@erosales in this case, you can create a measure like below

 

Did sales increase =
IF(
CALCULATE(SUM(sales[value]),DATEADD(sales[date].[Date],0,QUARTER))/CALCULATE(SUM(sales[value]),DATEADD(sales[date].[Date],-1,QUARTER))>0,"Yes","No")
 
below is the expected outcome
 
negi007_0-1638446628754.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


erosales
Frequent Visitor

Hi, I tried it but the formula is not working for me. The Q2 tag in your example should be 'No' in the computation that I'm looking for.

 

I modified your formula for the example below, I always got "Yes", because the DATEADD command (specifically --> calculate ( sum ('Table'[Sales]),dateadd ('Table'[Period],-1,MONTH)) -- seems to be returning a value of zero, when it should be -400 (for ABC, Jul vs Aug period). I tried PREVIOUSMONTH and PBI doesn't allow it.

 

Put another way, what formula will allow me to calculate the difference of account sales per period (Sales Delta column)?

 

PeriodAccountSalesDid Sales Increase?Sales Delta
7/1/2021ABC 1,000  
8/1/2021ABC    600No-400
9/1/2021ABC    800Yes200
10/1/2021ABC    900Yes100
7/1/2021XYZ    300  
8/1/2021XYZ    400Yes100
9/1/2021XYZ      90No-310
10/1/2021XYZ    200Yes110

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors