cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jarrod
Helper I
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)

 

Jarrod_0-1642464219167.png

 

Any help would be appreciated.

 

 

1 ACCEPTED SOLUTION
VahidDM
Super User
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:

VahidDM_0-1642464971653.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

2 REPLIES 2
Jarrod
Helper I
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!

 

VahidDM
Super User
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:

VahidDM_0-1642464971653.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!