Last working day/business day of Month (Friday)

[imranami]

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

[JarroVGIT · Accepted 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 🙂

[TAMOREIRA]

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. 

[AndySmith]

Hi - just wondering if you got any further forward with this querey? Looking for same thing!

[imranami]

@JarroVGITperfect thank you and for the prompt response.

@az38 thanks for responding. I needed the last working day for the month.

[JarroVGIT]

No problem, please mark it as the solution so others can find it easily as well 🙂
Thanks and good day!

[az38]

Hi @imranami 

try a column

Column2 = IF(WEEKDAY('Table'[Column1])=6;"It's Friday!";"-")

will it enough?

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn