Forum Discussion

Fyouri's avatar
Fyouri
Frequent Visitor
10 years ago
Solved

Problem with running total

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              T...
Need Help
  • sdjensen's avatar
    sdjensen
    10 years ago

    You could try something like this:

     

    CALCULATE(
	SUM( 'Table'[Total Hours] ),
	DATESBETWEEN(
		'DateTable'[Date],
		NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'DateTable'[Date]) ) ),
		LASTDATE ( 'DateTable'[Date] )
	)
)
Anonymous's avatar
Anonymous
Not applicable
10 years ago

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