Month over Month % issue

[Anonymous]

Hello -  Using the measure below, you can see that the month over month %'s are correct....until you get to September vs October.    Any idea why the Sept to Oct percentage is incorrect?    Should be 6.09%.     Is it because October is not a full month?    

Cumulative RMA Count MoM% =

VAR __PREV_MONTH = CALCULATE([Cumulative RMA Count], DATEADD('Date Table'[Date], -1, MONTH))

RETURN

    DIVIDE([Cumulative RMA Count] - __PREV_MONTH, __PREV_MONTH)

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Due to I don't know about your data model, I build a sample to have a test.

Measure:

Cumulative RMA Count = 
IF (
    SUM ( 'Date Table'[Value] ) = BLANK (),
    BLANK (),
    SUMX (
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date].[MonthNo] <= MAX ( 'Date Table'[Date].[MonthNo] )
        ),
        'Date Table'[Value]
    )
)

Cumulative RMA Count MoM% =
VAR __PREV_MONTH =
    CALCULATE (
        [Cumulative RMA Count],
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date].[MonthNo]
                <= MAX ( 'Date Table'[Date].[MonthNo] ) - 1
        )
    )
RETURN
    IF (
        SUM ( 'Date Table'[Value] ) = BLANK (),
        BLANK (),
        DIVIDE ( [Cumulative RMA Count] - __PREV_MONTH, __PREV_MONTH )
    )

Result:

You can download the pbix file from this link: Month over Month % issue

Best Regards,

Rico Zhou

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

[amitchandak]

@Anonymous , You formula seem correcy. As you are not using year in viusal. Make sure you do not have data for last year.

Also you can try Time intelligence functions

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[campbelldw]

What adjustments do I need to make to the MTDSales and Last MTDSales formulas to account for a Date Table that extends far past my data values (i.e. Date Table goes to 2025 but data values are for current 2021 months)?

For some reason when I use these formulas I receive blank values and it is typically because my Date Table forces the calculation to 2025 where all values are blank.

Happy to provide more context if needed.