Getting the increased or decreased value for Month Year column

[jswamy]

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!

[Jihwan_Kim · Accepted solution]

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

[vivek31 · Accepted solution]

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.

[jswamy]

Thanks Evreyone, got the solution for above answers.

[Anonymous · Accepted solution]

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!

[vivek31 · Accepted solution]

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.

[Jihwan_Kim · Accepted solution]

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