Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TomLU123
Helper III
Helper III

Count number of changes of the value

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. 

DatePrice
8/1/2018300
8/2/2018400
8/3/2018500
8/4/2018500
8/5/2018450
8/6/2018400
8/7/2018400

In this case, the price changed 4 times. 

 

Is it possible to build a measurement to count this number of changes?

Many thanks!

4 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@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 )

 


Regards
Zubair

Please try my custom visuals

View solution in original post

@TomLU123

 

Please see file attached

 

countchnages.png


Regards
Zubair

Please try my custom visuals

View solution in original post

@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 )

cncv.png


Regards
Zubair

Please try my custom visuals

View solution in original post

@TomLU123

 

See revised file with MEASURE attached

 

cnuv.png


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

@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 )

 


Regards
Zubair

Please try my custom visuals

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?

DateProduct Price
8/1/2018A300
8/2/2018A400
8/3/2018A450
8/1/2018B600
8/2/2018B600
8/3/2018B800
8/1/2018C500
8/2/2018C300
8/3/2018C200

 

Many thanks!

@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 )

cncv.png


Regards
Zubair

Please try my custom visuals

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. 

DateProduct PriceChange Tag
8/1/2018A3000
8/2/2018A4001
8/3/2018A4501
8/1/2018B6000
8/2/2018B6000
8/3/2018B8001
8/1/2018C5000
8/2/2018C3001
8/3/2018C2001

 

In that case, is it possible to modiy the DAX and ignore the first date in the range selected?

Many thanks!

@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 )

Regards
Zubair

Please try my custom visuals

@TomLU123

 

See revised file with MEASURE attached

 

cnuv.png


Regards
Zubair

Please try my custom visuals

@TomLU123

 

Please see file attached

 

countchnages.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.