MOM Error getting a straight line

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

Hi Anonymous,

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

Hi Anonymous,

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