cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Last working day/business day of Month (Friday)

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

1 ACCEPTED SOLUTION

Accepted Solutions
Senior Member

## Re: Last working day/business day of Month (Friday)

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 🙂

4 REPLIES 4
Super Contributor

## Re: Last working day/business day of Month (Friday)

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

Senior Member

## Re: Last working day/business day of Month (Friday)

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 🙂

Regular Visitor

## Re: Last working day/business day of Month (Friday)

@djerro123perfect thank you and for the prompt response.

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

Senior Member

## Re: Last working day/business day of Month (Friday)

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

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)