Line Chart displays wrong dates with TTM measure

[bdurham15]

Hello,

I have a standard line chart with a trendline that displays almost all of my measures, except for any "TTM" Measures that I have. Non date bound measures work ok, as well as measures that I am using TOTALMTD((QTD),(YTD)) with. Even though I have a consecutive series of months selected in a slicer above, when using the TTM measures, all months in the calendar are displayed.  I have been using the chart in published reports for weeks now, but recently updated my calendar table to run completely in query editor, so that I have to make little to no changes in calculated columns. When comparing to my old dataset, I can't seem to find what may be different or what is causing this problem. Also note that in this same report page I have the measures working properly in a table.

TTM Total Sales = 
CALCULATE (
    [Total Sales],
    DATESBETWEEN (
        'Calendar'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ),
        LASTDATE ( 'Calendar'[Date] )
    )
)

[v-yulgu-msft]

Hi @bdurham15,

Please set the X-axis type to Categorical for a test.

regards,

Yuliana Gu

[bdurham15]

@v-yulgu-msft, thank you for your response, unfortunately this does not correct the issue.

[bdurham15 · Accepted solution]

After doing some more digging Mr. Ferrari at SQLBI was able to help out. Adding this bolded and italicized portion to the forumla did away with the unwanted dates, and is also the most efficient way to make the calculation (compared to an IF statement that would excluded the dates not selected).

@v-yulgu-msft thank you for your help regardless!

TTM Total Sales = 
DIVIDE( [Total Sales], [Total Sales] ) * 
CALCULATE (
    [Total Sales],
    DATESBETWEEN (
        'Calendar'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ),
        LASTDATE ( 'Calendar'[Date] )
    )
)