topic Re: Getting the increased or decreased value for Month Year column in DAX Commands and Tips

Getting the increased or decreased value for Month Year column

jswamy — Thu, 26 Dec 2024 04:04:59 GMT

Hello Team,

I need a help with dax that Need to get increased or decreased sales count for a year month column.

If i select 2024 in slicer
Ex: 2024 Jan = 150
2024 Feb= 450
2024 Mar = 600 So, If i drag the visual and placed the measure and column the view will be like this.

Current Visual View :

But What i want here is need to get increased or decreased value. for ex 2023 Dec 100 sales is there and 2024 jan is having 150 sales So, 50 sales increased, Need to show 2024 Jan as 50 Sales and Feb is having 450 sales.. so from Jan to Feb it increased 300 sales has increased so, i need to show in 2024 Feb as 300... same for Mar, now its showing 550, from Feb to Mar it increased 100.. for Mar, need to show 100. and so on...

EXPECTED OUTPUT:

Please anyone help me with dax.

Thanks!

Re: Getting the increased or decreased value for Month Year column

Jihwan_Kim — Thu, 26 Dec 2024 04:40:04 GMT

Hi,

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

OFFSET function (DAX) - DAX | Microsoft Learn

expected result measure: = VAR _currentyearmonth = SUM ( sales[sales] ) VAR _previousyearmonth = CALCULATE ( SUM ( sales[sales] ), OFFSET ( -1, ALL ( 'calendar'[Year], 'calendar'[Year-Month], 'calendar'[Year-Month sort] ), ORDERBY ( 'calendar'[Year-Month sort], ASC ) ) ) RETURN _currentyearmonth - _previousyearmonth

Re: Getting the increased or decreased value for Month Year column

vivek31 — Thu, 26 Dec 2024 05:37:33 GMT

HI jswamy

first create a measure of total sales

total_sales = SUM(Sales[SalesAmount])

and second you can try this measure to create month of month diff

month diff = var previous_month = CALCULATE([total_sales],PREVIOUSMONTH('Calendar 2'[DateKey])) RETURN [total_sales] - previous_month

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Getting the increased or decreased value for Month Year column

Anonymous — Thu, 26 Dec 2024 07:10:53 GMT

Hi jswamy,

Thanks for the reply from Jihwan_Kim and vivek31.

To compare the sales with the previous month, please check the following points:

1. This is my sample data:

2. Add a measure named Output and write the DAX as below:

Output= VAR _currentYear = YEAR ( MAX ( 'SalesData'[Date] ) ) VAR _currentMonth = MONTH ( MAX ( 'SalesData'[Date] ) ) VAR _previousSales = CALCULATE ( SUM ( SalesData[Sales] ), FILTER ( ALL ( SalesData ), 'SalesData'[Date] >= DATE ( _currentYear, _currentMonth - 1, 1 ) && 'SalesData'[Date] < DATE ( _currentYear, _currentMonth, 1 ) ) ) RETURN SUM ( SalesData[Sales] ) - _previousSales

3. Here is the final testing result:

Please feel free to contact if there is any problem.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Getting the increased or decreased value for Month Year column

jswamy — Wed, 01 Jan 2025 04:09:42 GMT

Thanks Evreyone, got the solution for above answers.