Forum Discussion

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
7 years ago
Solved

How to calculate MOM% ?

Hi , below is my data sample. I want to use DAX formula to calculate the data in desired column(either column or measure). Then I can use the new column or measure to draw a line chart.

 

Thanks in advanced

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi ryan_mayu,

     

    I made one sample for your reference. Both ways as below.

     

    Measure:

     

    1. Create a dimtime table using the formula and create the relationship between the date table and the fact table.

     

     

    DIMTIME = CALENDARAUTO()

     

     

     

    2. Create the measure as below.

     

    Measure = VAR PRE = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH))
VAR CUR = CALCULATE(SUM(Table1[Amount]))
RETURN
IF(ISBLANK(PRE),0,(CUR-PRE)/PRE)

     

     

    Calculated column:

     

    Pre = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH))
    result = IF(ISBLANK(Table1[Pre]),0,(Table1[Amount]-Table1[Pre])/Table1[Pre])

     

    For more details, please check the pbix as attached.Regards,

    Frank

5 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi ryan_mayu,

     

    I made one sample for your reference. Both ways as below.

     

    Measure:

     

    1. Create a dimtime table using the formula and create the relationship between the date table and the fact table.

     

     

    DIMTIME = CALENDARAUTO()

     

     

     

    2. Create the measure as below.

     

    Measure = VAR PRE = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH))
VAR CUR = CALCULATE(SUM(Table1[Amount]))
RETURN
IF(ISBLANK(PRE),0,(CUR-PRE)/PRE)

     

     

    Calculated column:

     

    Pre = CALCULATE(SUM(Table1[Amount]),DATEADD(Table1[Date],-1,MONTH))
    result = IF(ISBLANK(Table1[Pre]),0,(Table1[Amount]-Table1[Pre])/Table1[Pre])

     

    For more details, please check the pbix as attached.Regards,

    Frank

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      7 years ago

      Hi Ashish,

       

      Thanks for your help. However, I am not sure why I can't access to the onedrive website. Could you please attach the pbix file in the reply?

       

      Thanks

      Ryan