cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Compute the last Thursday of the Month (not filter)

Hi Folks,

I have a table with the following columns: Claim_id, Claim_amount_usd, & Claim_date. Last_Thursday is a calculated column.

 Claim_id Claim_amount_usd Claim_date Last_Thursday 1 105 Thursday, May 28, 2020 Thursday, May 28, 2020 2 300 Wednesday, May 27, 2020 Thursday, May 28, 2020 3 321 Friday, May 22, 2020 Thursday, May 28, 2020 4 9093 Monday, May 4, 2020 Thursday, May 28, 2020 5 1931 Wednesday, Apri 29, 2020 6 432 Friday, April 24, 2020 7 2091 Thursday, April, 16, 2020 8 10039 Monday, April, 13 2020 9 892 Tuesday, April 7, 2020

I've tried the DAX code pasted below from a solution to a similar problem. However the problem with this code is that the FILTER method seems to need an actual claim entry on that date. This results in the output shown above. I'd like to compute the date for the last Thursday regardless of whether or not there is an entry on that date. So, the empty rows in [Last_Thursday] column are filled with "Thursday, April 30, 2020" for instance.

Here is the code I tried below, which does not solve this:

Last Thursday =

VAR a = 'Claim_table'[Claim_date]

VAR y = FILTER(

ALL('Claim_table'[Claim_date]),

YEAR('Claim_table'[Claim_date]) = YEAR(a) &&

MONTH('Claim_table'[Claim_date]) = MONTH(a) -1 &&

DAY([Claim_date] > 23 &&

WEEKDAY([Claim_date],3) = 3)

RETURN MAXX(y,[Claim_date])

Any thoughts on how to make that work? Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

Re: Compute the last Thursday of the Month (not filter)

``Last Thrusday= Var _v = EOMONTH('Claim_table'[Claim_date],0) return _v +-1*if(WEEKDAY(_v )<5,WEEKDAY(_v )+2,WEEKDAY(_v )-5)``

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

2 REPLIES 2
Highlighted
Super User IV

Re: Compute the last Thursday of the Month (not filter)

``Last Thrusday= Var _v = EOMONTH('Claim_table'[Claim_date],0) return _v +-1*if(WEEKDAY(_v )<5,WEEKDAY(_v )+2,WEEKDAY(_v )-5)``

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Compute the last Thursday of the Month (not filter)

@amitchandak wrote:

``Last Thrusday= Var _v = EOMONTH('Claim_table'[Claim_date],0) return _v +-1*if(WEEKDAY(_v )<5,WEEKDAY(_v )+2,WEEKDAY(_v )-5)``

This works great! Thank you so much!

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors