Forum Discussion
Helper IShow Week ending date without all week day dates
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]) )
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
ryan_mayuSuper User
the table you provided contains measures?
could you pls provide some sample data?
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.