How to create a categorical column based on the quarterly growth of price?

archienono — Thu, 13 Jan 2022 17:55:31 GMT

Hi,

I have sales data like the following:

Date	Product	Price	Sales
2020.04.01	AAA	60	600
2020.05.01	AAA	60	900
2020.09.01	AAA	70	700

I want to get a report based on the price changes:

Category	Time	Total Sales
New Sales	2020Q2	1500
Price Increase	2020Q3	700

So far, I'm able to create new measures based on the quarterly price change:

VAR __PREV_QUARTER = CALCULATE( AVERAGE('pricing'[price]), DATEADD('pricing'[date].[Date], -1, QUARTER) ) VAR __NEXT_QUARTER = IF(DATEDIFF(LASTDATE(pricing[date].[Date]),TODAY(),QUARTER)>1, CALCULATE( AVERAGE('pricing'[price]), DATEADD('pricing'[date].[Date], 1, QUARTER) ) ,1 ) VAR QuarterChange = AVERAGE('pricing'[price]) - __PREV_QUARTER RETURN IF( __PREV_QUARTER&&__NEXT_QUARTER,IF(QuarterChange>0,"Increase",IF(QuarterChange<0,"Decrease", "No change")), "New win / churn"

But the measures cannot be put as Rows header (my speculation is that measures needs the overall input from a table).

Can someone suggest what can be done in DAX to categorize the quarterly price change trend?

Thanks.

Re: How to create a categorical column based on the quarterly growth of price?

lbendlin — Sat, 15 Jan 2022 15:49:27 GMT

You are mixing two slightly unrelated concepts. One is the price change and the other is the quarterly view.

I would start by grouping events first by product price, and then by quarter, as price changes can happen multiple times in a quarter.

Please provide more comprehensive sample data if possible.

topic How to create a categorical column based on the quarterly growth of price? in DAX Commands and Tips

How to create a categorical column based on the quarterly growth of price?

Re: How to create a categorical column based on the quarterly growth of price?