Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
2 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

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

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

Best Regards

manojk_pbi's avatar
manojk_pbi
Helper V
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

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

    Best Regards