cancel
Showing results for
Did you mean:
Frequent Visitor

## MOM Error getting a straight line

Hi Everyone

Can any one help me in achieving the MOM % trend in the below scenario.

I am using the trend of 13 months using the below formula.(Creating the what if parameter).

Formula:- Order 13 months =

CALCULATE (
SUM(Orders[Sales]),
DATESINPERIOD('Date'[Date], MAX ('Date'[Date]), -'N'[N Value],MONTH))

the above formula is working for sum of sales but when i use it for MOM it is showing only one month value or a straight line.

Order MOM 13 months =
CALCULATE (
[Sales MoM%],
DATESINPERIOD('Date'[Date], MAX ('Date'[Date]), -'N'[N Value],MONTH))

Attaching the PBIX file for reference.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: MOM Error getting a straight line

Hi @Akhil555,

On the chart select the 3 dots and sort by month in the PBIX file it's sort by value.

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: MOM Error getting a straight line

Got it

@MFelix, I have found a solution for this by creating a sort column in summary Table.

DAX formula:-

YEAR('Summary Table'[EndOfMonht])*100+MONTH('Summary Table'[EndOfMonht])

Thanks,
Akhil.

9 REPLIES 9
Super User

## Re: MOM Error getting a straight line

Hi @Akhil555,

First of all I can't get the file from the location you are pointing to, believe it's better to send a onedrive, google drive or dropbox, or something similar links than a facebook group.

Looking at your measure I assume you are using the SQL Jason Last N months sales post  are you placing the correct columns on your visual to have the correct result?

In is post Jason creates two columns on the sales table (MonthYear and MonthYearNo) using the RELATED formula to the Calendar table and this are the ones that are used on the barchart not the month from the calendar table and the  slicer is from the calendar.

Check if this is your issue and if you have any problems please share the file through one of the ways I refer above since I'm not abble to retrieve the file from facebook.

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: MOM Error getting a straight line

Hi @MFelix,

Thanks for response.

Iam using the SQL Jason Last N months sales post , placing the right columns in the bar chart.

Regards,

Akhil.

Super User

## Re: MOM Error getting a straight line

Hi @Akhil555,

I have look at your issue and believe that the issue is regarding the fact that when you make the context be reduce to last N months you won't be abble to get the previous year value.

I have a solution that maybe cannot be that one you want because go to adding an additional table, but it works, and the additional table only as 59 rows so not much information because it's summarize by month.

Create a column on the Date table with the following code:

`EndOfMonht = EOMONTH('Date'[Date];0)`

Create the a summary table:

```Summary Table =
DISTINCT ( 'Date'[EndOfMonht] );
"CurrentYear"; [Sales Total];
"PreviousYear"; CALCULATE ([Sales Total]; DATEADD ( 'Date'[Date]; -1; YEAR ) ));[CurrentYear] <>BLANK())```

Relate it to the date table and then add the following measures:

```Order 13 months measure =
CALCULATE (
SUM('Summary Table'[CurrentYear]);
DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH))

MOM % 13 Months =
CALCULATE (
(SUM('Summary Table'[CurrentYear]) - SUM('Summary Table'[PreviousYear])) / SUM('Summary Table'[PreviousYear]);
DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH))
```

Check the PBIX file attach.

Again this solution may not be what you want since we are adding a new table but it works and it's dinamic so when you refresh the data it will also refresh.

Regards,

MFelix

Proud to be a Datanaut!

Super User

## Re: MOM Error getting a straight line

Hi @Akhil555,

Try the following.

`EndOfMonht = EOMONTH('Date'[Date];0) // just used to make the summary table`

Add a table with the following code:

```Summary Table =
DISTINCT ( 'Date'[EndOfMonht] );
"CurrentYear"; [Sales Total];
"PreviousYear"; CALCULATE ([Sales Total]; DATEADD ( 'Date'[Date]; -1; YEAR ) ));[CurrentYear] <>BLANK())```

```Sales Total = SUM(Orders[Sales])

MOM % 13 Months =
CALCULATE (
(SUM('Summary Table'[CurrentYear]) - SUM('Summary Table'[PreviousYear])) / SUM('Summary Table'[PreviousYear]);
DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH))

Order 13 months measure =
CALCULATE (
SUM('Summary Table'[CurrentYear]);
DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH))```

Then just place them on your visual.

Not sure if this is what you need since it's adding a table, but to what I can check when adding the rolling average the context is lost for getting the previous year values. The new table only has 59 rows, so not a big number of information could work.

Check PBIX attach

Regards,

MFelix

Proud to be a Datanaut!

Super User

## Re: MOM Error getting a straight line

Hi @Akhil555,

I have found a solution for this problem, however yesterday I have tried to publish my solution and my post was deleted from the forum, don't understand why.

I will try to redo the post later.

Sorry for taking so long to answer.

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: MOM Error getting a straight line

Hi @MFelix,

Thanks for the solution. Its perfectly working.

Could you please to sort the trend, trend is not getting sorted by Month and Year?

Regards.

Akhil.

Super User

## Re: MOM Error getting a straight line

Hi @Akhil555,

On the chart select the 3 dots and sort by month in the PBIX file it's sort by value.

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

## Re: MOM Error getting a straight line

Hi @MFelix,

I have tried this by sorting Month and year, but the trend is not sorted.

Attaching the screenshot below. Do you have any other solution for this.

Frequent Visitor

## Re: MOM Error getting a straight line

Got it

@MFelix, I have found a solution for this by creating a sort column in summary Table.

DAX formula:-

YEAR('Summary Table'[EndOfMonht])*100+MONTH('Summary Table'[EndOfMonht])

Thanks,
Akhil.