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

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
manojk_pbi
Helper V
Helper V

How to write DAX for % change

Hi Friends,

 

I am looking for a DAX measure to calculate Change in Percentage w.r.t to a particular period by Products. Please suggest me how can we write the optimized DAX for the same. Provided a sample with required output. Baseline is Jan-23 data, this can vary as per business request. Any suggestions are much appreciated.

MY            Product   Qty          %Change

Jan-23PROD1100 
Feb-23PROD112020%
Mar-23PROD113535%
Apr-23PROD114040%
May-23PROD112020%
Jun-23PROD11000%
Jul-23PROD180-20%
Aug-23PROD190-10%
Sep-23PROD175-25%
Oct-23PROD11000%
Nov-23PROD180-20%
Dec-23PROD165-35%

 

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @manojk_pbi ,

Please update the formula of measure as below to get it, please find the details in the attachment.

%Change = 
VAR _MY =
    SELECTEDVALUE ( 'Table'[MY] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _preMY =
    CALCULATE (
        MIN ( 'Table'[MY] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _product )
    )
VAR _preqty =
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] = _product
                && 'Table'[MY] = _preMY
        )
    )
RETURN
    IF (
        _MY = _preMY,
        BLANK (),
        DIVIDE ( SUM ( 'Table'[Qty] ) - _preqty, _preqty )
    )

vyiruanmsft_0-1710837746157.png

Best Regards

View solution in original post

7 REPLIES 7
manojk_pbi
Helper V
Helper V

Thanks for your solution. It's perfect. 

manojk_pbi
Helper V
Helper V

Thanks for your solution. It's perfect. 

lbendlin
Super User
Super User

That is a very, very subjective topic especially when sign changes are involved (like in your case).

 

One approximation is DIVIDE(current-previous, ABS(previous),BLANK())

 

But at the end of the day you have to decide what is a reasonable number in your scenarios.

Thanks for your input.

 

I am looking for suggestion on writing measure to calculate % Change .

Anonymous
Not applicable

@lbendlin  Thanks for your contribution on this thread.

Hi @manojk_pbi ,

@lbendlin already gave the related formula. If you want to get the expected result base on your sample data, you can create a measure as below to get it:

%Change = 
VAR _MY =
    SELECTEDVALUE ( 'Table'[MY] )
VAR _preMY =
    CALCULATE ( MIN ( 'Table'[MY] ), ALLSELECTED ( 'Table' ) )
VAR _preqty =
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[MY] = _preMY )
    )
RETURN
    IF (
        _MY = _preMY,
        BLANK (),
        DIVIDE ( SUM ( 'Table'[Qty] ) - _preqty, _preqty )
    )

vyiruanmsft_0-1710836008977.png

Best Regards

@Anonymous , this is perfect. Thanks for the working sample. If i were had to add Product into filter to calculate % Change for each product what changes to be made here ? 

Pls suggest

Anonymous
Not applicable

Hi @manojk_pbi ,

Please update the formula of measure as below to get it, please find the details in the attachment.

%Change = 
VAR _MY =
    SELECTEDVALUE ( 'Table'[MY] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _preMY =
    CALCULATE (
        MIN ( 'Table'[MY] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _product )
    )
VAR _preqty =
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product] = _product
                && 'Table'[MY] = _preMY
        )
    )
RETURN
    IF (
        _MY = _preMY,
        BLANK (),
        DIVIDE ( SUM ( 'Table'[Qty] ) - _preqty, _preqty )
    )

vyiruanmsft_0-1710837746157.png

Best Regards

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors