cancel
Showing results for
Did you mean:
Regular Visitor

## Last trading day in the week

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: Last trading day in the week

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]
CALCULATE(
MAX('Table1'[Date])
, ALLEXCEPT('Table1' , 'Table1'[Week Ending])
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

Hopefully this is what you are looking for.

Regards,

Tom

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
2 REPLIES 2
Super User III

## Re: Last trading day in the week

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]
CALCULATE(
MAX('Table1'[Date])
, ALLEXCEPT('Table1' , 'Table1'[Week Ending])
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

Hopefully this is what you are looking for.

Regards,

Tom

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Highlighted
Regular Visitor

## Re: Last trading day in the week

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors