topic Re: Need Help _ DAX Week starting and Week ending total score for students in DAX Commands and Tips

Need Help _ DAX Week starting and Week ending total score for students

Rt0790 — Wed, 27 Dec 2023 19:39:03 GMT

Hi All, I need your help your help writing a DAX statement (conditional/custom column) or Power query steps for the same to achieve the following result, here's a table that I am working on , Thank you so much in advance.

Student Name	Score	Year	Week	WeekEnding	StudentID
John	85.2	2023	39	Sunday, Sep 24, 2023	121
John	85.2	2023	40	Sunday, Oct 1, 2023	121
John	51.7	2023	41	Sunday, Oct 8, 2023	121
John	54.3	2023	42	Sunday, Oct 15, 2023	121
John	38.3	2023	43	Sunday, Oct 22, 2023	121
John	20	2023	44	Sunday, Oct 29, 2023	121
John	41.9	2023	45	Sunday, Nov 5, 2023	121
John	50.4	2023	46	Sunday, Nov 12, 2023	121
John	30.9	2023	47	Sunday, Nov 19, 2023	121
John	52.6	2023	48	Sunday, Nov 26, 2023	121
John	0	2023	49	Sunday, Dec 3, 2023	121

I need the week starting and ending running totals of the score column, Please see the expected result table.

Expected result

Student Name	Score	Year	Week	WeekEnding	StudentID	Score Week Beginning	Score Week Ending
John	85.2	2023	39	Sunday, Sep 24, 2023	121	0	85.2
John	85.2	2023	40	Sunday, Oct 1, 2023	121	85.2	170.4
John	51.7	2023	41	Sunday, Oct 8, 2023	121	170.4	222.1
John	54.3	2023	42	Sunday, Oct 15, 2023	121	222.1	276.4
John	38.3	2023	43	Sunday, Oct 22, 2023	121	276.4	314.7
John	20	2023	44	Sunday, Oct 29, 2023	121	314.7	334.7
John	41.9	2023	45	Sunday, Nov 5, 2023	121	334.7	376.6
John	50.4	2023	46	Sunday, Nov 12, 2023	121	376.6	427
John	30.9	2023	47	Sunday, Nov 19, 2023	121	427	457.9
John	52.6	2023	48	Sunday, Nov 26, 2023	121	457.9	510.5
John	0	2023	49	Sunday, Dec 3, 2023	121	510.5	510.5

Re: Need Help _ DAX Week starting and Week ending total score for students

123abc — Thu, 28 Dec 2023 05:22:33 GMT

To achieve the expected result of calculating the running totals of the score column from the week starting to the week ending for each student, you can use DAX formulas in Power BI. Here's how you can create a new table or a calculated column to get the desired result:

Create a New Table or Use a Calculated Column: You can create a new calculated table or use a calculated column to generate the running totals for each student based on the week starting and week ending.

Here's a DAX formula for the calculated table approach:

ExpectedResultTable =
VAR CurrentStudent = 'YourTableName'[Student Name]
VAR CurrentWeek = 'YourTableName'[Week]
RETURN
ADDCOLUMNS(
FILTER(
'YourTableName',
[Student Name] = CurrentStudent && [Week] <= CurrentWeek
),
"Score Week Beginning",
CALCULATE(
SUM('YourTableName'[Score]),
FILTER(
'YourTableName',
[Student Name] = CurrentStudent && [Week] <= CurrentWeek
)
),
"Score Week Ending",
CALCULATE(
SUM('YourTableName'[Score]),
FILTER(
'YourTableName',
[Student Name] = CurrentStudent && [Week] <= CurrentWeek
)
)
)

Replace 'YourTableName': Make sure to replace 'YourTableName' with the actual name of your table where you have the student data.
Apply the DAX Formula:

Go to Power BI Desktop.
Create a new table using the DAX formula provided above.
You will get a new table with columns "Score Week Beginning" and "Score Week Ending" that will give you the running totals based on the week starting and week ending for each student.

This DAX formula will calculate the running totals of the score column for each student from the starting week up to the current week, giving you the expected result table as shown in your example.

Re: Need Help _ DAX Week starting and Week ending total score for students

v-jincheng-msft — Thu, 28 Dec 2023 09:59:10 GMT

Hi Rt0790,

Hi, thanks for the information you have given.

Please use the following DAX to create Calculated columns:

Score Week Beginning = VAR CurrentWeek = 'Table'[Week] VAR CurrentScore = 'Table'[Score] RETURN IF( CurrentWeek = MIN('Table'[Week]), 0, CALCULATE( SUM('Table'[Score]), FILTER( ALL('Table'), 'Table'[Week] < CurrentWeek ) ) )

Score Week Ending = VAR CurrentWeek = 'Table'[Week] VAR CurrentScore = 'Table'[Score] RETURN IF( CurrentWeek = MIN('Table'[Week]), CurrentScore, CALCULATE( SUM('Table'[Score]), FILTER( ALL('Table'), 'Table'[Week] <= CurrentWeek ) ) )

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Joseph Ji

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