cancel
Showing results for
Did you mean:
Helper I

Cumulative Total Per Group Per Week

Hi All,

Struggling to create a measure or calculated column to get the Cumulative Total per Group per week. The report can only display one week at a time. I have a date table containing week number.

Dataset example is below (w/o the CumulativeValue)

Any help would be appreciated.

1 ACCEPTED SOLUTION
Super User

Hi @Jarrod

If your tables are connected via DateID, first add a new column to the Actuals Table:

``````Week Number =
RELATED('Date Table'[WeekNumber])``````

then use this code to add a new column for Cumulative Value:

``````Cumulative Value =
CALCULATE (
SUM ( 'Actuals Table'[Value] ),
FILTER (
ALLEXCEPT (
'Actuals Table',
'Actuals Table'[Metric],
'Actuals Table'[Week Number]
),
'Actuals Table'[DateID] <= EARLIER ( 'Actuals Table'[DateID] )
)
)``````

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

2 REPLIES 2
Helper I

Hi @VahidDM,

Thank you for your response. This works perfectly! I have also added in the Year to the Filter section as it was adding up last year values.

Thanks for your help!

Super User

Hi @Jarrod

If your tables are connected via DateID, first add a new column to the Actuals Table:

``````Week Number =
RELATED('Date Table'[WeekNumber])``````

then use this code to add a new column for Cumulative Value:

``````Cumulative Value =
CALCULATE (
SUM ( 'Actuals Table'[Value] ),
FILTER (
ALLEXCEPT (
'Actuals Table',
'Actuals Table'[Metric],
'Actuals Table'[Week Number]
),
'Actuals Table'[DateID] <= EARLIER ( 'Actuals Table'[DateID] )
)
)``````

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Announcements

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.