Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

MOM Error getting a straight line

Hi Everyone:smileyhappy:   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).   ...
General Comment
Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous,

     

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

     

    Regards,

    MFelix

  • Anonymous's avatar
    Anonymous
    7 years ago

    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.
     
MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi Anonymous,

 

 

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