Compute the last Thursday of the Month (not filter)

[ndeshpande]

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

[amitchandak · Accepted solution]

@ndeshpande 

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

[ndeshpande]

---

@amitchandak wrote:

@ndeshpande 

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!