Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |