Problem with running total

[Fyouri]

I have a list where i keep track of the total number of hours worked in a company per month. I need to get a sum of the last 12 months  on each row. This is what i would want:

Date              Total Hours               Cumulated hours

1/1/2014                  5000                                   5000

1/2/2014                  6000                                 11000

..

1/12/2014               8000                                185000 (total of 1/1/2014 till 1/12/2014)

1/1/2015                 7000                                187000 (total of 1/2/2014 till 1/1/2015)

I don't really know if I should do this in a column or a Measure. This is what i have so far: Calculate(Sum('Table'[Total Hours]),DATEADD('Table'[Date],-1,YEAR)<'Table'[Date])). But this is not working. Can someone push me a bit in the right direction please?

[sdjensen · Accepted solution]

You could try something like this:

CALCULATE(
	SUM( 'Table'[Total Hours] ),
	DATESBETWEEN(
		'DateTable'[Date],
		NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'DateTable'[Date]) ) ),
		LASTDATE ( 'DateTable'[Date] )
	)
)

[Fyouri]

Sdjensen,

I tried this but this gives me just the same value as the column total hours.

[sdjensen]

Do you have a seperate table with all the dates from the first date in your data to the last and made a relationship between these 2 tables?

[Fyouri]

Thanks Sdjensen,

I forgot to make a connection between the 2 tables. It works now.

[Anonymous]

Hi 

Try this 

Last6SalesNew:=IF(ISBLANK(sum(SalesData[SalesAmount])),BLANK(),
CALCULATE(sum(SalesData[SalesAmount]),
DATESBETWEEN(
Calendar[FullDate],
FIRSTDATE(PARALLELPERIOD(Calendar[FullDate], -6, MONTH)),
LASTDATE(PARALLELPERIOD(Calendar[FullDate], -1, MONTH))
), ALL(Calendar) ))

This gives me the sales for the previous six months from current month displayed in the pivot table or charts.

Replace the formulas with the columns in your data model.

This assumes a date table called as Calendar and is linked to the SalesData date field.

Best 

Cheenusing