Last trading day in the week

[Anonymous]

Hi,

I'm trying to show the last trading day date in a given week. Anyone any idea how I could create another column to display this? See calendar details below:

Date	Week Num	Year	Week Ending	Month	Day of Week	Day	Trading Day	Last Trading Day
31 December 2019	13	2020	02 January 2020	December	5	Tuesday	1	02/01/2020
30 December 2019	13	2020	02 January 2020	December	4	Monday	1	02/01/2020
29 December 2019	13	2020	02 January 2020	December	3	Sunday	0	02/01/2020
28 December 2019	13	2020	02 January 2020	December	2	Saturday	0	02/01/2020
27 December 2019	13	2020	02 January 2020	December	1	Friday	1	02/01/2020
26 December 2019	12	2020	26 December 2019	December	7	Thursday	0	24/12/2019
25 December 2019	12	2020	26 December 2019	December	6	Wednesday	0	24/12/2019
24 December 2019	12	2020	26 December 2019	December	5	Tuesday	1	24/12/2019
23 December 2019	12	2020	26 December 2019	December	4	Monday	1	24/12/2019
22 December 2019	12	2020	26 December 2019	December	3	Sunday	0	24/12/2019
21 December 2019	12	2020	26 December 2019	December	2	Saturday	0	24/12/2019
20 December 2019	12	2020	26 December 2019	December	1	Friday	1	24/12/2019
19 December 2019	11	2020	19 December 2019	December	7	Thursday	1	19/12/2019
18 December 2019	11	2020	19 December 2019	December	6	Wednesday	1	19/12/2019
17 December 2019	11	2020	19 December 2019	December	5	Tuesday	1	19/12/2019
16 December 2019	11	2020	19 December 2019	December	4	Monday	1	19/12/2019
15 December 2019	11	2020	19 December 2019	December	3	Sunday	0	19/12/2019
14 December 2019	11	2020	19 December 2019	December	2	Saturday	0	19/12/2019
13 December 2019	11	2020	19 December 2019	December	1	Friday	1	19/12/2019

As you can see, for week 12 it shows the last trading day as 24th December, even though the week ends on the 26th December. This is because the 25th and 26th aren't UK trading days.

Is there a way to create this "Last trading day" column using a DAX formula?

Any help will be much appreciated!

Aaron

[TomMartens · Accepted solution]

Hey,

based on the sample data you provided, I created a calculated column using this DAX statement below.
The underlying assumptions to make the DAX statement work are:

• the columns Date and Week Ending are of data type date or datetime
• the column Trading Day is of data type whole number or decimal

Is Last Trading Day = 
var _Date = 'Table1'[Date]
var _lastTradingDatePerWeek = 
CALCULATE(
    MAX('Table1'[Date])
    , ALLEXCEPT('Table1' , 'Table1'[Week Ending])
    , 'Table1'[Trading Day] = 1)
return
IF(_Date = _lastTradingDatePerWeek , "yes" , "no")

The table will look like this:

If you want to return the date, just return the _lastTradingDatePerWeek instead of the final IF statement:

...
return
_lastTradingDatePerWeek

Hopefully this is what you are looking for.

Regards,

Tom

[Anonymous]

Thanks, Tom! Very helpful!