cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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_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 IX
Super User IX

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 IX
Super User IX

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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors