Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Experts,
I am building a report to monitor the price trend. I wish to create a measurement to count the number of changes that the price has. The dataset is like below.
Date | Price |
8/1/2018 | 300 |
8/2/2018 | 400 |
8/3/2018 | 500 |
8/4/2018 | 500 |
8/5/2018 | 450 |
8/6/2018 | 400 |
8/7/2018 | 400 |
In this case, the price changed 4 times.
Is it possible to build a measurement to count this number of changes?
Many thanks!
Solved! Go to Solution.
You can create a calculated column like and then use sum of this column
Column = VAR PreviousPriceRow = TOPN ( 1, FILTER ( Table1, [Date] < EARLIER ( [Date] ) ), [Date], DESC ) VAR PreviousPrice = MINX ( PreviousPriceRow, [Price] ) RETURN IF ( OR ( [Price] = PreviousPrice, ISBLANK ( PreviousPrice ) ), 0, 1 )
Please see file attached
You can modify it like this.
Change in Red Font
Column = VAR PreviousPriceRow = TOPN ( 1, FILTER ( Table1, [Product] = EARLIER ( [Product] ) && [Date] < EARLIER ( [Date] ) ), [Date], DESC ) VAR PreviousPrice = MINX ( PreviousPriceRow, [Price] ) RETURN IF ( OR ( [Price] = PreviousPrice, ISBLANK ( PreviousPrice ) ), 0, 1 )
See revised file with MEASURE attached
You can create a calculated column like and then use sum of this column
Column = VAR PreviousPriceRow = TOPN ( 1, FILTER ( Table1, [Date] < EARLIER ( [Date] ) ), [Date], DESC ) VAR PreviousPrice = MINX ( PreviousPriceRow, [Price] ) RETURN IF ( OR ( [Price] = PreviousPrice, ISBLANK ( PreviousPrice ) ), 0, 1 )
Hi @Zubair_Muhammad,
Thanks for your great help!
Just one question: if now there is one more column in the data set that I wish to count the number of price changes of each product, how should we modify the expression and achieve that?
Date | Product | Price |
8/1/2018 | A | 300 |
8/2/2018 | A | 400 |
8/3/2018 | A | 450 |
8/1/2018 | B | 600 |
8/2/2018 | B | 600 |
8/3/2018 | B | 800 |
8/1/2018 | C | 500 |
8/2/2018 | C | 300 |
8/3/2018 | C | 200 |
Many thanks!
You can modify it like this.
Change in Red Font
Column = VAR PreviousPriceRow = TOPN ( 1, FILTER ( Table1, [Product] = EARLIER ( [Product] ) && [Date] < EARLIER ( [Date] ) ), [Date], DESC ) VAR PreviousPrice = MINX ( PreviousPriceRow, [Price] ) RETURN IF ( OR ( [Price] = PreviousPrice, ISBLANK ( PreviousPrice ) ), 0, 1 )
Hi @Zubair_Muhammad,
Just a further question: If there is a date filter to allow the user to select the time range they want to see, we will face following situation:
If they select Product A, and filter the date from 8/2/2018 to 8/3/2018, the Sum will be 2. But actually, the price only changes 1 time in those two days.
Date | Product | Price | Change Tag |
8/1/2018 | A | 300 | 0 |
8/2/2018 | A | 400 | 1 |
8/3/2018 | A | 450 | 1 |
8/1/2018 | B | 600 | 0 |
8/2/2018 | B | 600 | 0 |
8/3/2018 | B | 800 | 1 |
8/1/2018 | C | 500 | 0 |
8/2/2018 | C | 300 | 1 |
8/3/2018 | C | 200 | 1 |
In that case, is it possible to modiy the DAX and ignore the first date in the range selected?
Many thanks!
In that case, we have to use a MEASURE
Measure =
VAR temp =
ADDCOLUMNS (
Table1,
"Price Change",
VAR PreviousPriceRow =
TOPN ( 1, FILTER ( Table1, [Date] < EARLIER ( [Date] ) ), [Date], DESC )
VAR PreviousPrice =
MINX ( PreviousPriceRow, [Price] )
RETURN
IF ( OR ( [Price] = PreviousPrice, ISBLANK ( PreviousPrice ) ), 0, 1 )
)
RETURN
COUNTX ( FILTER ( temp, [Price Change] = 1 ), 1 )
See revised file with MEASURE attached
Please see file attached
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |