growth % over month on month

[santoshlearner2]

Dear All,

I have a measure for growth % over previous month, but when i want to display this in a graph / Matrix over month on month.

I have the following measure

Current Stock: Calculate(sum(stock, month)

Previous Stock: CALCULATE( current stock,PREVIOUSMONTH(month)

Growth% = DIVIDE( ( current stock-  previous stock), previous stock)

When i put in the card visual it is perfect, but in matrix if i do, i get infinity error/

Slicer selections: year / then user has option to select Months.

Pls advise what wrong i am doing.

[mdaatifraza5556 · Accepted solution]

Hi @santoshlearner2 

Could you please try the below dax to create a measure ?

Please ensure that your date table is marked as date table.

Growth % =
VAR CurrentStock = [Current Stock]
VAR PreviousStock =
CALCULATE(
[Current Stock],
PREVIOUSMONTH('Date'[Date]) 
)
RETURN
IF(
ISBLANK(PreviousStock) || PreviousStock = 0,
BLANK(),
DIVIDE(CurrentStock - PreviousStock, PreviousStock)
)

If this answer your questions, kindly accept it as a solution and give kudos.

[FarhanJeelani · Accepted solution]

Hi @santoshlearner2 ,

Infinity/blank in MoM usually comes from dividing by zero (or by a blank previous value) when there is no data for the previous month in the current filter context. The fix is to calculate the previous month stock robustly and guard the division.

Recommended approach (works well with year slicer and month drill-down)

Create a MoM growth measure that uses a separate PreviousMonth calculation and returns blank (or 0) when there is no previous value.

Growth % MoM = VAR CurrentStock = [Current Stock]

VAR PreviousStock = CALCULATE([Current Stock], PREVIOUSMONTH('Date'[Date])) RETURN IF(ISBLANK(PreviousStock) || PreviousStock = 0, BLANK(), DIVIDE(CurrentStock - PreviousStock, PreviousStock) )

Notes:

[Current Stock] should be a stable measure (e.g., CALCULATE(SUM(Stock), ...)) that respects the visual’s date context.
'Date'[Date] must come from a proper Date table marked as a Date table, with a relationship to your fact table on the date/key field.

PREVIOUSMONTH will work within the current visual context; if there’s no data for the previous month in the current filter, PreviousStock will be blank.

If you prefer to handle cross-year smoothly (sometimes PREVIOUSMONTH yields blanks when your slicers exclude the previous month), you can use DATEADD instead:

Growth % MoM (DATEADD) = VAR CurrentStock = [Current Stock] VAR PreviousStock = CALCULATE([Current Stock], DATEADD('Date'[Date], -1, MONTH)) RETURN IF(ISBLANK(PreviousStock) || PreviousStock = 0, BLANK(), DIVIDE(CurrentStock - PreviousStock, PreviousStock) )

Please mark this post as solution if it hepls you. Appreciate Kudos.

[mdaatifraza5556 · Accepted solution]

Hi @santoshlearner2 

Could you please try the below dax to create a measure ?

Please ensure that your date table is marked as date table.

Growth % =
VAR CurrentStock = [Current Stock]
VAR PreviousStock =
CALCULATE(
[Current Stock],
PREVIOUSMONTH('Date'[Date]) 
)
RETURN
IF(
ISBLANK(PreviousStock) || PreviousStock = 0,
BLANK(),
DIVIDE(CurrentStock - PreviousStock, PreviousStock)
)

If this answer your questions, kindly accept it as a solution and give kudos.

[santoshlearner2]

Hi

getting blank error, 

Thanks

[MasonMA · Accepted solution]

Ensure your date table is properly marked as a Date Table and use a continuous date column. Then try 

Growth % MOM =
VAR CurrentStock = [Current Stock]
VAR PreviousStock =
CALCULATE(
[Current Stock],
DATEADD('Date'[Date], -1, MONTH)
)
RETURN
IF(
ISBLANK(PreviousStock) || PreviousStock = 0,
BLANK(),
DIVIDE(CurrentStock - PreviousStock, PreviousStock)
)

[FarhanJeelani · Accepted solution]

Hi @santoshlearner2 ,

Infinity/blank in MoM usually comes from dividing by zero (or by a blank previous value) when there is no data for the previous month in the current filter context. The fix is to calculate the previous month stock robustly and guard the division.

Recommended approach (works well with year slicer and month drill-down)

Create a MoM growth measure that uses a separate PreviousMonth calculation and returns blank (or 0) when there is no previous value.

Growth % MoM = VAR CurrentStock = [Current Stock]

VAR PreviousStock = CALCULATE([Current Stock], PREVIOUSMONTH('Date'[Date])) RETURN IF(ISBLANK(PreviousStock) || PreviousStock = 0, BLANK(), DIVIDE(CurrentStock - PreviousStock, PreviousStock) )

Notes:

[Current Stock] should be a stable measure (e.g., CALCULATE(SUM(Stock), ...)) that respects the visual’s date context.
'Date'[Date] must come from a proper Date table marked as a Date table, with a relationship to your fact table on the date/key field.

PREVIOUSMONTH will work within the current visual context; if there’s no data for the previous month in the current filter, PreviousStock will be blank.

If you prefer to handle cross-year smoothly (sometimes PREVIOUSMONTH yields blanks when your slicers exclude the previous month), you can use DATEADD instead:

Growth % MoM (DATEADD) = VAR CurrentStock = [Current Stock] VAR PreviousStock = CALCULATE([Current Stock], DATEADD('Date'[Date], -1, MONTH)) RETURN IF(ISBLANK(PreviousStock) || PreviousStock = 0, BLANK(), DIVIDE(CurrentStock - PreviousStock, PreviousStock) )

Please mark this post as solution if it hepls you. Appreciate Kudos.