cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
imranami Regular Visitor
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
djerro123 Senior Member
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 🙂

View solution in original post

4 REPLIES 4
az38 Super Contributor
Super Contributor

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

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

djerro123 Senior Member
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 🙂

View solution in original post

imranami Regular Visitor
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.

djerro123 Senior Member
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!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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

Difinity Conference

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)