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 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 |
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.
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:
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:
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.
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 |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |