compare sales and percentage changes in sales

[Anonymous]

Hi Team,

Please refer the below data and refer the below questions

Required DAX measuere for below questions

1. Compare sales in previous months

2.how much percentage increased/decreased month on month basis on the sales 

Thanks

[Poojara_D12]

Hi @Anonymous 

To help you with the DAX measures for comparing sales in previous months and calculating the percentage increase/decrease in sales month-over-month, I'll break down each question and provide the corresponding DAX expressions.

1. Compare Sales in Previous Months

To compare the sales in the current month with the previous month, we need to calculate the total sales for both the current month and the previous month.

Assuming you have a Sales table with a SalesAmount column, and the Date table is linked properly to your Sales table, you can use the following measure to calculate sales for the previous month:

Sales in Previous Month

DAX
Sales Previous Month = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    PREVIOUSMONTH(Dim_Calendar[Date])
)

• This measure uses the PREVIOUSMONTH function to shift the date context to the previous month and then sums the sales for that period.

Sales in Current Month

If you want to show the sales for the current month as well (if not already available), you can simply use this measure:

DAX
Sales Current Month = 
SUM(Sales[SalesAmount])

2. Percentage Increase/Decrease in Sales Month on Month

To calculate the percentage change in sales month-over-month (MoM), you can use the following measure:

Percentage Change in Sales

DAX
Percentage Change in Sales = 
VAR CurrentSales = SUM(Sales[SalesAmount])
VAR PreviousSales = 
    CALCULATE(
        SUM(Sales[SalesAmount]),
        PREVIOUSMONTH(Dim_Calendar[Date])
    )
RETURN 
    IF(
        NOT(ISBLANK(PreviousSales)),
        (CurrentSales - PreviousSales) / PreviousSales,
        BLANK()
    )

Explanation:

• CurrentSales: This variable holds the sales for the current month.
• PreviousSales: This variable calculates the sales for the previous month using the PREVIOUSMONTH function.
• The formula (CurrentSales - PreviousSales) / PreviousSales gives the percentage change.
• The IF condition ensures that the measure doesn't return a value when the previous month’s sales are blank (e.g., for the first month in the dataset).

Summary of Measures

1. Sales in Previous Month: Sales Previous Month = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSMONTH(Dim_Calendar[Date]))
2. Sales in Current Month: Sales Current Month = SUM(Sales[SalesAmount])
3. Percentage Change in Sales:

DAX
Percentage Change in Sales = 
VAR CurrentSales = SUM(Sales[SalesAmount])
VAR PreviousSales = 
    CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSMONTH(Dim_Calendar[Date]))
RETURN 
    IF(NOT(ISBLANK(PreviousSales)), (CurrentSales - PreviousSales) / PreviousSales, BLANK())​

This should provide you with a way to compare sales across months and calculate the percentage change month on month. Let me know if you need any further adjustments!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales: = 
SUM( sales[sales] )

OFFSET function (DAX) - DAX | Microsoft Learn

Previous month sales: = 
CALCULATE (
    [Sales:],
    OFFSET (
        -1,
        ALL ( 'calendar'[Month-Year], 'calendar'[Year-Month sort] ),
        ORDERBY ( 'calendar'[Year-Month sort], ASC )
    )
)

[Irwan]

hello @Anonymous 

please check if this accomodate your need.

1. create measure for your 1st query with following DAX

Query 1 =
var _Month = SELECTEDVALUE('Table'[Month])
Return
SUM('Table'[Sales])

2. create measure for your 2st query with following DAX

Query 2 =
var _PreviousMonth = CALCULATE([Query 1],'Table'[Month]<MAX('Table'[Month]))
var _CurrentMonth = CALCULATE([Query 1],'Table'[Month]=MAX('Table'[Month]))
Return
DIVIDE(
    _CurrentMonth,
    _PreviousMonth
)

Hope this will help.

Thank you.