Running total

[Anonymous]

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 March 2016.

I used following formula, which doesn't sum up the 12 months data.

RN = CALCULATE(COUNT(DATA[Risk Number]),DATESINPERIOD(DATA[Date Occurred],Lastdate(DATA[Date Occurred]),-12,MONTH))

Appreciate if anyone has a solution for it. 

[v-haibl-msft]

@Anonymous

Does above solution work on you side?

Best Regards,
Herbert

[Anonymous]

Hi Herbert @v-haibl-msft,

Unfortunately, it doesn't work. It only returns the total of the month not the aggregate value of last 12 months.

[v-haibl-msft]

@Anonymous

That's strange. It works in my attached .pbix file. Could you please share your .pbix file through online file service like OneDrive? So that I can look into it.

Best Regards,
Herbert

[Anonymous]

Hi Herbert @v-haibl-msft

Check the link below ;

https://drive.google.com/open?id=0B-NaZUyqRQMoNTlBQ3BQOUU4dzQ

Regards,

Thilan

[v-haibl-msft · Accepted solution]

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

@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