Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative total by quarters

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.