compare sales and percentage changes in sales

Anonymous — Sat, 09 Nov 2024 10:36:29 GMT

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

Re: compare sales and percentage changes in sales

Irwan — Sun, 10 Nov 2024 01:38:44 GMT

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.

Re: compare sales and percentage changes in sales

Jihwan_Kim — Sun, 10 Nov 2024 05:54:25 GMT

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 ) ) )

Re: compare sales and percentage changes in sales

Poojara_D12 — Mon, 11 Nov 2024 09:49:08 GMT

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

Sales in Previous Month: Sales Previous Month = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSMONTH(Dim_Calendar[Date]))
Sales in Current Month: Sales Current Month = SUM(Sales[SalesAmount])
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

topic compare sales and percentage changes in sales in DAX Commands and Tips

compare sales and percentage changes in sales

Re: compare sales and percentage changes in sales

Re: compare sales and percentage changes in sales

Re: compare sales and percentage changes in sales

1. Compare Sales in Previous Months

Sales in Previous Month

Sales in Current Month

2. Percentage Increase/Decrease in Sales Month on Month

Percentage Change in Sales

Summary of Measures