cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aaron92 Regular Visitor
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:

 

DateWeek NumYearWeek EndingMonthDay of WeekDayTrading DayLast Trading Day
31 December 201913202002 January 2020December5Tuesday102/01/2020
30 December 201913202002 January 2020December4Monday102/01/2020
29 December 201913202002 January 2020December3Sunday002/01/2020
28 December 201913202002 January 2020December2Saturday002/01/2020
27 December 201913202002 January 2020December1Friday102/01/2020
26 December 201912202026 December 2019December7Thursday024/12/2019
25 December 201912202026 December 2019December6Wednesday024/12/2019
24 December 201912202026 December 2019December5Tuesday124/12/2019
23 December 201912202026 December 2019December4Monday124/12/2019
22 December 201912202026 December 2019December3Sunday024/12/2019
21 December 201912202026 December 2019December2Saturday024/12/2019
20 December 201912202026 December 2019December1Friday124/12/2019
19 December 201911202019 December 2019December7Thursday119/12/2019
18 December 201911202019 December 2019December6Wednesday119/12/2019
17 December 201911202019 December 2019December5Tuesday119/12/2019
16 December 201911202019 December 2019December4Monday119/12/2019
15 December 201911202019 December 2019December3Sunday019/12/2019
14 December 201911202019 December 2019December2Saturday019/12/2019
13 December 201911202019 December 2019December1Friday119/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
Highlighted
Super User III
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]
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:

image.png

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

 

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Highlighted
Super User III
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]
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:

image.png

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

 

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

aaron92 Regular Visitor
Regular Visitor

Re: Last trading day in the week

Thanks, Tom! Very helpful!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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

January 2020 Community Highlights

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

Top Solution Authors
Top Kudoed Authors