Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |