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.

     

4 Replies

  • 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:]
)

     

  • ahadkarimi's avatar
    ahadkarimi
    Icon for Solution Specialist rankSolution Specialist
    2 years ago

    Hi Sekharnaga, give this a try, and if you encounter any issues, let me know.
    Replace "Table" with the name of your table and "DateColumn" with the name of your column.

    Difference_Current_Prev_Month = 
VAR CurrentMonthPercentage = SELECTEDVALUE(Table[Percentage])
VAR PreviousMonthPercentage = 
    CALCULATE(
        SELECTEDVALUE(Table[Percentage]),
        PREVIOUSMONTH(Table[DateColumn])
    )
RETURN
    CurrentMonthPercentage - PreviousMonthPercentage

     

    Did I answer your question? If so, please mark my post as the solution! ✔️
    Your Kudos are much appreciated! Proud to be a Solution Specialist!

  • Sekharnaga's avatar
    Sekharnaga
    Frequent Visitor
    2 years ago

    Hi, I already have created meassure for Percentage. Now i want one more table to show the differnce between previous month and current month. 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.