Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

@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 on linkedin

View solution in original post

5 REPLIES 5
negi007
Community Champion
Community Champion

@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 on linkedin

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!

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
Community Champion
Community Champion

@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 on linkedin

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.