Forum Discussion

Rt0790's avatar
Rt0790
Regular Visitor
2 years ago

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

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 y...
Dax power query
v-jincheng-msft's avatar
v-jincheng-msft
Icon for Community Support rankCommunity Support
2 years ago

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.