Forum Discussion
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 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?
You could try something like this:
CALCULATE( SUM( 'Table'[Total Hours] ), DATESBETWEEN( 'DateTable'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'DateTable'[Date]) ) ), LASTDATE ( 'DateTable'[Date] ) ) )
5 Replies
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
You could try something like this:
CALCULATE( SUM( 'Table'[Total Hours] ), DATESBETWEEN( 'DateTable'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'DateTable'[Date]) ) ), LASTDATE ( 'DateTable'[Date] ) ) )
