Show Week ending date without all week day dates

[thomazinh]

I need to display the week ending date for each week without showing each date within the week. I have a measurement accurately categorizing each date by its week ending date. Weeks are Monday through Sunday, where Mondays indicate the start of the week. 

I would expect the following week ending dates to be: 

trx date	week ending date	data
5/7/24	5/12/24	8
5/8/24	5/12/24	8
5/9/24	5/12/24	10
5/10/24	5/12/24	8
5/11/24	5/12/24	11
5/12/24	5/12/24	12
5/13/24	5/19/24	8
5/14/24	5/19/24	8
5/15/24	5/19/24	12
5/16/24	5/19/24	12
5/17/24	5/19/24	12
5/18/24	5/19/24	10

I only want to show the week ending date in my table view. I'd expect it to be:

week ending date	data
5/12/24	57
5/19/24	62

However, my DAX is summing up BOTH weeks when I only include the week ending date DAX and not the trx date. This is what it returns: 

week ending date	data
5/19/24	119

How can I write a DAX to display all instances of the week ending date without showing the trx date?

This is a certified dataset and I am not the dataset owner, so I do not have access to create a calculated column. Any help?

Here is my week ending code:

Weekday End of Week4 = CALCULATE( MAX('Table'[TrxDate]) - WEEKDAY(MAX('Table'[TrxDate]), 2) + 7, ALL('Table'[TrxDate]) )

[v-kongfanf-msft · Accepted solution]

Hi @thomazinh ,

You can create calculated table to get the most direct results.

Table 2 =
VAR table_ =
    SUMMARIZECOLUMNS ( 'Table'[week ending date] )
RETURN
    ADDCOLUMNS (
        table_,
        "a",
            CALCULATE (
                SUM ( 'Table'[data] ),
                ALLEXCEPT ( 'Table', 'Table'[week ending date] )
            )
    )

Best Regards,
Adamk Kong

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

[ryan_mayu]

the table you provided contains measures?

could you pls provide some sample data?