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:
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!
Go to Solution.
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:
Is Last Trading Day =
var _Date = 'Table1'[Date]
var _lastTradingDatePerWeek =
, ALLEXCEPT('Table1' , 'Table1'[Week Ending])
, 'Table1'[Trading Day] = 1)
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:
Hopefully this is what you are looking for.
View solution in original post
Thanks, Tom! Very helpful!
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!