Count number of changes of the value

[TomLU123]

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!

[Zubair_Muhammad · Accepted solution]

@TomLU123

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 )

[Zubair_Muhammad · Accepted solution]

@TomLU123

Please see file attached

[Zubair_Muhammad · Accepted solution]

@TomLU123

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 )

[Zubair_Muhammad · Accepted solution]

@TomLU123

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 )

[TomLU123]

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!

[Zubair_Muhammad · Accepted solution]

@TomLU123

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 )

[TomLU123]

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!

[Zubair_Muhammad]

@TomLU123

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 )

[Zubair_Muhammad · Accepted solution]

@TomLU123

See revised file with MEASURE attached

[Zubair_Muhammad · Accepted solution]

@TomLU123

Please see file attached