Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Last Thrusday= Var _v = EOMONTH('Claim_table'[Claim_date],0) return _v +-1*if(WEEKDAY(_v )<5,WEEKDAY(_v )+2,WEEKDAY(_v )-5)
Last Thrusday= Var _v = EOMONTH('Claim_table'[Claim_date],0) return _v +-1*if(WEEKDAY(_v )<5,WEEKDAY(_v )+2,WEEKDAY(_v )-5)
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |