Forum Discussion

ADSL's avatar
ADSL
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

MoM% Showing the Infinity Value

Hi BI Community Team,

 

I have a sales order table and looking for MTD, PMTD & MoM Growth % with measure below that I explore and apply.

 

MTD-Sales = TOTALMTD(SUM('Sales Order'[Amt]), 'CALENDAR'[Date])

 

PMTD-Sales = TOTALMTD(SUM('Sales Order'[Amt]), DATEADD('CALENDAR'[Date], -1, MONTH))
 
MoM-Sales = ([MTD-Sales] - [PMTD-Sales])/[PMTD-Sales]
 
But MoM-Sales, it's not showing correctly if we choose the slicer in Jan'24 as the screenshot below.
 
 
Any suggestion/advise?
 
Thanks and Regards,
Need Help
  • pmreis's avatar
    pmreis
    2 years ago

    Hi ADSL ,

    This typically occurs when you divide a number by zero. In this case, the problem arises when the [PMTD-Sales] measure returns a value of 0, which is then used as the denominator in your MoM-Sales calculation.

    Try something like this:

     

    MoM-Sales = 
VAR CurrentMonthSales = [MTD-Sales]
VAR PreviousMonthSales = [PMTD-Sales]
RETURN
IF(
    PreviousMonthSales = 0,
    BLANK(),
    (CurrentMonthSales - PreviousMonthSales) / PreviousMonthSales
)

     

    For reference on error handling in DAX formulas: DAX error reference


    Pedro Reis - Data Platform MVP / MCT
    Making Power BI and Fabric Simple

    If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

    Find me at LinkedIn

     

4 Replies

  • pmreis's avatar
    pmreis
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    Hi ADSL ,

    This typically occurs when you divide a number by zero. In this case, the problem arises when the [PMTD-Sales] measure returns a value of 0, which is then used as the denominator in your MoM-Sales calculation.

    Try something like this:

     

    MoM-Sales = 
VAR CurrentMonthSales = [MTD-Sales]
VAR PreviousMonthSales = [PMTD-Sales]
RETURN
IF(
    PreviousMonthSales = 0,
    BLANK(),
    (CurrentMonthSales - PreviousMonthSales) / PreviousMonthSales
)

     

    For reference on error handling in DAX formulas: DAX error reference


    Pedro Reis - Data Platform MVP / MCT
    Making Power BI and Fabric Simple

    If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

    Find me at LinkedIn

     

    • ADSL's avatar
      ADSL
      Icon for Post Prodigy rankPost Prodigy
      2 years ago

      Hi pmreis ,

       

      May I need your help and advise of Card visual? That's still showing as the screenshot below, it should be zero -- 0.00 or 0%.

       

      Thanks and Regard,

       

      • pmreis's avatar
        pmreis
        Icon for Most Valuable Professional rankMost Valuable Professional
        2 years ago

        Hi ADSL 

        You have everything right on this file you shared! It's just that the period you selected (January) doesn't have a previous month, hence the calculation for the previous month doesn't work, neither the variations as you don't have a previous month value.

        Try another month and you will see it works just fine.

         


        Pedro Reis - Data Platform MVP / MCT
        Making Power BI and Fabric Simple

        If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

        Find me at LinkedIn