Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Month over Month % issue

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)

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      campbelldw
      New Member
      5 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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