MoM% Calculation

[Anonymous]

Hi All,

I'm trying to create a MoM% calculation but for some reasons its not giving correct output.

Can anyone help me here?

Measure Used:

MQR MoM% =

    VAR __PREV_MONTH =

        CALCULATE(

            [MQR],

            DATEADD('F_MARKETING_RESPONSE'[Month-Year].[Date], -1, MONTH)

        )

    RETURN

        DIVIDE([MQR] - __PREV_MONTH, __PREV_MONTH)

[Anonymous · Accepted solution]

hmm Ok, but that doesnt solve my problem to represnt data in a visual like below

However I found a work around by creating a different calendar table and making few measures.

[negi007]

@Anonymous 

i can see your formuala is slighly incorrect. You can create a measure like below

MoM Vol Change =
var cm_vol=CALCULATE([sales)],DATEADD('Calendar'[Date],0,MONTH))
var pm_vol = CALCULATE([sales],DATEADD('Calendar'[Date],-1,MONTH))
return
DIVIDE(cm_vol,pm_vol,0)

[Anonymous]

Its not giving me the correct output

If I get the previous Month MQR column correct then MoM% can be easily calculated but this column is not giving me correct values and I have almost tried every function. Not sure what the issue is.

I'm attaching link to the sample PBIX file.

Let me know in case you find any issue in the model.

https://drive.google.com/file/d/1jUark0E2v0z56RQjIeOZBERkrL7loHuC/view?usp=share_link

[Padycosmos]

Hope this is the result you are looking for

[Anonymous]

Yes this is exactly what I'm looking for.

Kindly let me know how you calculated the previous month MQR?

[Padycosmos]

I have used the measure that you already created. However, in the visualization, instead of Month_year column, I have used the Year & Month from the date hierarchy of the FULL_DATE column

[Anonymous · Accepted solution]

hmm Ok, but that doesnt solve my problem to represnt data in a visual like below

However I found a work around by creating a different calendar table and making few measures.

[negi007 · Accepted solution]

@Anonymous  there seems to be an issue with the dates in your table. so keep it simple. i have created a seperate date table which will be used to filter the data from the main table.

i have also created calc column in the dimdate table like below

you can add more calc column as per your need in the dimdate table. Below is the result you were looking for

let me know if it works for you. thanks