Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Running total

Hi All,   I want to create a running total (Not YTD) based on previous 12 months. eg; in May 2017 result should be the count of data points from April 2016 and April 2017 count of data points from ...
Data Modeling
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Anonymous

     

    Please create a relationship between the Calendar and LTI table. Then use YearMonth in the X-Axis of the Line chart as below.

     

     

    Best Regards,
    Herbert

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

Anonymous

 

Please try with following two measures.

 

Total_1 = 
IF (
    ISBLANK ( SUM ( Table1[Sales] ) ),
    BLANK (),
    CALCULATE (
        SUM( Table1[Sales] ),
        DATESBETWEEN (
            'Calendar'[Date],
            FIRSTDATE ( DATEADD ( 'Calendar'[Date], -11, MONTH ) ),
            LASTDATE ( 'Calendar'[Date] )
        )
    )
)

Total_2 = 
IF (
    ISBLANK ( SUM ( Table1[Sales] ) ),
    BLANK (),
    CALCULATE (
        SUM( Table1[Sales] ),
        DATESBETWEEN (
            'Calendar'[Date],
            FIRSTDATE ( DATEADD ( 'Calendar'[Date], -12, MONTH ) ),
            ENDOFMONTH ( DATEADD ( 'Calendar'[Date], -1, MONTH ) )
        )
    )
)

 

Best Regards,
Herbert