Forum Discussion

Sekharnaga's avatar
Sekharnaga
Frequent Visitor
2 years ago
Solved

Difference between current month and previous month Percentage

I have a monthwise Percentage data, when i filter month on the top, it is required to show the difference between current month and previous month Percentage. Please help with DAX function
Learn Together
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Sekharnaga ,

     

    Based on your description, I created an example using this sample data:

     

    Create measure, calculate the percentage for the current month of the current year.

    MEASURE = 
VAR _month =
    SELECTEDVALUE ( 'financials'[Month Number] )
VAR _year =
    SELECTEDVALUE ( financials[Year] )
VAR _sum1 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        'financials'[Month Number] = _month
            && 'financials'[Year] = _year
    )
VAR _sum2 =
    CALCULATE (
        SUM ( financials[Gross Sales] ),
        'financials'[Month Number] = _month
            && 'financials'[Year] = _year
    )
RETURN
    DIVIDE ( _sum1, _sum2 )


    Create a measure, get the percent for the previous month, and calculate the difference.

    Measure2 = 
VAR _month =
    SELECTEDVALUE ( 'financials'[Month Number] )
VAR _year =
    SELECTEDVALUE ( financials[Year] )
VAR _sum1 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        'financials'[Month Number] = _month - 1
            && 'financials'[Year] = _year
    )
VAR _sum2 =
    CALCULATE (
        SUM ( financials[Gross Sales] ),
        'financials'[Month Number] = _month - 1
            && 'financials'[Year] = _year
    )
VAR _pre =
    DIVIDE ( _sum1, _sum2 )
RETURN
    IF ( _pre = BLANK (), BLANK (), [MEASURE] - _pre )

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

 

Sales ratio: = 
VAR _currentyear =
    MAX ( 'calendar'[Year] )
VAR _salesyearly =
    CALCULATE (
        SUM ( sales[sales] ),
        FILTER (
            ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort], 'calendar'[Year] ),
            'calendar'[Year] = _currentyear
        )
    )
VAR _sales =
    SUM ( sales[sales] )
RETURN
    DIVIDE ( _sales, _salesyearly )

 

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

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

 

 

Difference: = 
IF (
    [Previous month sales ratio:],
    [Sales ratio:] - [Previous month sales ratio:]
)