Forum Discussion

santoshlearner2's avatar
santoshlearner2
Icon for Resolver II rankResolver II
1 year ago
Solved

Month on month percentage changes

Dear All,

Requesting your assistance, i have created a measure to calculate total sales / and also previous month sales, I have a date table, relationships are created, in my slicer i have  Financial year / Month / Product and i want a  matrix visual or a graph showing trend of last year or so. 

Issue:

1)  By using the values which gets computed shows only for the month which is selected in the slicer, it gives -100% error for all the months, it works only when the the invidual month is selected. Pls let me know what wrong i am doing.

2)I wanted to show a visual which shows the months and the respective Growth %, 

3) By using the measure which you suggest, can i get the values ie Growth % at category level also.

 

Measure for total sales: 

= CALCULATE(sum(sales, DATESMTD(DIMDatesQ[EOM]))
 
Measure for previous month: 
Previous Month Inflows = CALCULATE([measure for total sales],DATEADD(DIMDatesQ[Date],-1,MONTH))

Growth %
% growth inflows 1m = Measure for total sales /Previous Month Inflows)-1
 
Thank you all for taking time to reply.
 
Warm Regards
 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi santoshlearner2  - First create a clean Total Sales measure

    Total Sales =
    SUM ( Sales[SalesAmount] )

    Now create a previous month sale:

     

    Previous Month Sales =
    CALCULATE (
    [Total Sales],
    DATEADD ( DIMDatesQ[Date], -1, MONTH )
    )

     

    Hree we used DATEADD.

    Last measure for growth calculation as below:

    Growth % =
    DIVIDE ( [Total Sales] - [Previous Month Sales], [Previous Month Sales] )

     

    Hope this helps 

  • Shahid12523's avatar
    Shahid12523
    1 year ago

    - Use PARALLELPERIOD instead of DATEADD to get previous month values reliably across slicers.

    - Wrap your MoM % like this to avoid -100% errors:

     

    MoM Growth % = IF(
    ISBLANK([Previous Month Sales]),
    BLANK(),
    DIVIDE([Total Sales] - [Previous Month Sales], [Previous Month Sales])
    )

     

    - Works in matrix or line chart with Month on axis and Product or Category as legend.
    - For category-level MoM %, use ALLEXCEPT(Product, Product[Category]) inside CALCULATE.

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi santoshlearner2  - First create a clean Total Sales measure

    Total Sales =
    SUM ( Sales[SalesAmount] )

    Now create a previous month sale:

     

    Previous Month Sales =
    CALCULATE (
    [Total Sales],
    DATEADD ( DIMDatesQ[Date], -1, MONTH )
    )

     

    Hree we used DATEADD.

    Last measure for growth calculation as below:

    Growth % =
    DIVIDE ( [Total Sales] - [Previous Month Sales], [Previous Month Sales] )

     

    Hope this helps 

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    - Use PARALLELPERIOD instead of DATEADD to get previous month values reliably across slicers.

    - Wrap your MoM % like this to avoid -100% errors:

     

    MoM Growth % = IF(
    ISBLANK([Previous Month Sales]),
    BLANK(),
    DIVIDE([Total Sales] - [Previous Month Sales], [Previous Month Sales])
    )

     

    - Works in matrix or line chart with Month on axis and Product or Category as legend.
    - For category-level MoM %, use ALLEXCEPT(Product, Product[Category]) inside CALCULATE.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Ensure that there is a relationship (Many to One and Single) from the Date column of the fACt table to the Date column of the Calendar table.  Also, to your visual, drag Year and Month name from the Calendar table.  This simple pattern should work

    Measure = sum(Data[sales])

    PM = calculate([Measure],previousmonth(calendar[date]))

    Hope this helps.