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

[Anonymous]

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.

Quarter	Account	Sales	Did Sales Increase vs Prev Month?
Q1	ABC	1,000
Q2	ABC	600	No
Q3	ABC	800	Yes
Q4	ABC	900	Yes
Q1	XYZ	300
Q2	XYZ	400	Yes
Q3	XYZ	90	No
Q4	XYZ	200	Yes

Any help will be greatly appreciated.

Thanks!

[negi007 · Accepted solution]

@Anonymous 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

i have also attached pbix file for your reference.

[Anonymous]

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!

[Anonymous]

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]

@Anonymous 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

[Anonymous]

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)?

Period	Account	Sales	Did Sales Increase?	Sales Delta
7/1/2021	ABC	1,000
8/1/2021	ABC	600	No	-400
9/1/2021	ABC	800	Yes	200
10/1/2021	ABC	900	Yes	100
7/1/2021	XYZ	300
8/1/2021	XYZ	400	Yes	100
9/1/2021	XYZ	90	No	-310
10/1/2021	XYZ	200	Yes	110