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 Community,
Is it possible to create a variable within a measure that returns the date of the last Thursday of the current month dynamically, so for example, we are now in October 2022 and the date of the last Thursday of this month is 27th October 2022. Next month it will be 24th November 2022 etc.
Preferably, this would be calculated independant of any tables if possible?
Solved! Go to Solution.
Figured it out:
VAR __Start = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __END = EOMONTH ( TODAY (), 0 )
VAR __LastThursdayofCurrentMonth =
MAXX (
FILTER (
ADDCOLUMNS ( CALENDAR ( __Start, __END ), "Day", WEEKDAY ( [Date] ) ),
[Day] = 5
),
[Date]
)
Figured it out:
VAR __Start = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __END = EOMONTH ( TODAY (), 0 )
VAR __LastThursdayofCurrentMonth =
MAXX (
FILTER (
ADDCOLUMNS ( CALENDAR ( __Start, __END ), "Day", WEEKDAY ( [Date] ) ),
[Day] = 5
),
[Date]
)
i
If you have a date table make sure that you have the Weekday number (Weekday(Date[date])
and after the measure is :
calculate(max(date[date]), Weekday=4)
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 |
---|---|
97 | |
96 | |
82 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |