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 ...
  • 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