Forum Discussion
Cumulative total by quarters
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.
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.
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 !
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.