Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@erosales you can modify your measure like below
i have also attached pbix file for your reference.
Proud to be a Super User!
@erosales you can modify your measure like below
i have also attached pbix file for your reference.
Proud to be a Super User!
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!
@erosales in this case, you can create a measure like below
Proud to be a Super User!
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 |
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |