Forum Discussion
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 ...
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 , 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
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