Forum Discussion

bdurham15's avatar
bdurham15
Frequent Visitor
8 years ago
Solved

Line Chart displays wrong dates with TTM measure

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 ...
Need Help
  • bdurham15's avatar
    bdurham15
    8 years ago

    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] )
    )
)

     

     

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi bdurham15,

 

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

 

regards,

Yuliana Gu

bdurham15's avatar
bdurham15
Frequent Visitor
8 years ago

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

  • bdurham15's avatar
    bdurham15
    Frequent Visitor
    8 years ago

    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] )
    )
)