cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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_idClaim_amount_usdClaim_dateLast_Thursday
1105Thursday, May 28, 2020Thursday, May 28, 2020
2300Wednesday, May 27, 2020Thursday, May 28, 2020
3321Friday, May 22, 2020Thursday, May 28, 2020
49093Monday, May 4, 2020Thursday, May 28, 2020
51931Wednesday, Apri 29, 2020 
6432Friday, April 24, 2020 
72091Thursday, April, 16, 2020 
810039Monday, April, 13 2020 
9892Tuesday, 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
Super User IV

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

@ndeshpande 

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!

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

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

@ndeshpande 

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!

View solution in original post

Highlighted
Frequent Visitor

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


@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!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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