How to calculate MOM% ?

[ryan_mayu]

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

[v-frfei-msft · Accepted solution]

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

[AmolJalda]

Thanks @v-frfei-msft, 

This measure worked as expected...

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu]

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

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached.

[v-frfei-msft · Accepted solution]

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