cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Akhil555 Frequent Visitor
Frequent Visitor

MOM Error getting a straight line

Hi EveryoneSmiley Happy

 

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.

 

Link to File: https://www.facebook.com/groups/powerbi/?multi_permalinks=2399590703388408&notif_id=1543064655543768...

 

Thanks in Advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Akhil555 Frequent Visitor
Frequent Visitor

Re: MOM Error getting a straight line

Got itSmiley Happy

 

@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
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Akhil555 Frequent Visitor
Frequent Visitor

Re: MOM Error getting a straight line

Hi @MFelix,

 

Thanks for response.

 

Sharing via ondrive:-  https://nablerin-my.sharepoint.com/:u:/g/personal/akhil_j_nabler_com/EWcyrBqwH3dNqhRROiXUe4kBxhV5xlR...

 

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

 

Regards,

Akhil.

Super User
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 = 
FILTER(ADDCOLUMNS (
    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))

Then just add the measures to your chart.

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: MOM Error getting a straight line

Hi @Akhil555,

 

Try the following.

 

Add a column to your date table:

 

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

Add a table with the following code:

 

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

 

 

Then add the following measures:

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Akhil555 Frequent Visitor
Frequent Visitor

Re: MOM Error getting a straight line

Hi @MFelix,

 

Thanks for the solutionSmiley Wink. Its perfectly working.

 

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

 

Regards.

Akhil.

Super User
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Akhil555 Frequent Visitor
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.

 

MOM Sort.PNG

 

 

 

Akhil555 Frequent Visitor
Frequent Visitor

Re: MOM Error getting a straight line

Got itSmiley Happy

 

@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.