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 team,
I want add a calculated column to a date table which identifies whether a date is the last working day for that month (Friday in my scenario).
Best regards,
ImranAmi
Solved! Go to Solution.
I created a date table with the following boolean column:
DateTable = ADDCOLUMNS(CALENDAR("1/1/2019", "31/12/2019"),
"isWorkDay", IF(WEEKDAY([Date], 2) > 5, FALSE, TRUE())
)
This returns wether a day is a workday or not.
The following calculated column returns only true on the last workday of that month:
IsLastWorkDay =
VAR currentDate = DateTable[Date]
RETURN
IF(AND(COUNTROWS(FILTER(DateTable,
DateTable[Date] > currentDate && DateTable[Date] <= EOMONTH(currentDate, 0) && DateTable[isWorkDay] = TRUE)) = 0, DateTable[isWorkDay] = TRUE), TRUE, FALSE)
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
I created a date table with the following boolean column:
DateTable = ADDCOLUMNS(CALENDAR("1/1/2019", "31/12/2019"),
"isWorkDay", IF(WEEKDAY([Date], 2) > 5, FALSE, TRUE())
)
This returns wether a day is a workday or not.
The following calculated column returns only true on the last workday of that month:
IsLastWorkDay =
VAR currentDate = DateTable[Date]
RETURN
IF(AND(COUNTROWS(FILTER(DateTable,
DateTable[Date] > currentDate && DateTable[Date] <= EOMONTH(currentDate, 0) && DateTable[isWorkDay] = TRUE)) = 0, DateTable[isWorkDay] = TRUE), TRUE, FALSE)
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
Hi JarroVGIT, if I wanted to get the first working day of the month, where should I edit the formula? I have tried multiple approaches and although it has produced a few good things, I don't have the column I need yet.
Hi - just wondering if you got any further forward with this querey? Looking for same thing!
@JarroVGITperfect thank you and for the prompt response.
@az38 thanks for responding. I needed the last working day for the month.
No problem, please mark it as the solution so others can find it easily as well 🙂
Thanks and good day!
Proud to be a Super User!
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |