Cumulative total by quarters

[Anonymous]

Hi, 

I'm trying to compute a cumulative total by quarter. 

My table has 4 columns : Entity (A, B, C, etc.) // Year (2017, 2018, 2019, etc.) // Quarter (Q1, Q2, Q3, Q4) // Values

I woud like to add a column that computes the sum of all values for the same entity, same quarter, previous years. 

For example, in my row Entity B, 2019, Q3, I'd like the column to add up the Entity B, Q3 values for years 2017, 2018 and 2019.

Any idea ? 

Thank you guys

[Anonymous]

Assuming that you have no other tables in your data model, this should work:

Measure:=
	CALCULATE(
		SUM(<TableName>[Values])
		,ALL(<TableName>, <TableName>[Year])
	)

Essentially, we're removing the current filter context on the Year column, but preserving the filter context on the Entity and Quarter columns.

Just make sure to replace <TableName> with the actual name of your table.

I'd recommend having actual dates in your fact table and then making a relationship to a calendar table that has Year, Quarter, Month, etc.  This will give you MUCH for flexibility in the long run.

[Anonymous]

Thank you for your answer. 

While doing this, I get on each row the same value as in the column "value". 

Where is the condition that rows to be considered in the sum should have a year less or equal to current row ? 

Thanks a lot ! 

[Anonymous · Accepted solution]

My apologies, missed that conditional statement:

Measure:=
VAR CurrentYear = 
	MAX(<TableName>[Year])
RETURN
CALCULATE(
	SUM(<TableName>[Values])
	,FILTER(
		ALL(<TableName>[Year]),
		<TableName>[Year] <= CurrentYear
	)
)

This first identifies the year in the current filter context, and then only includes years that are less than or equal to that value.