Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
kitpalmer
Frequent Visitor

Calculate Last Working Day of each month in a Calendar Table

I have a Calendar Table in PBI that I need to add a field for Last Working Day of each month.  I know for each date if it is a working day, holiday or weekend, but I need identify when the last day of the month falls on a weekend or holiday and roll transaction from those days back to the last working day to summarize several reports correctly.

 

kitpalmer_0-1630602210946.png

 

My goal is to add a calculated column that would filter/group by "Year", "Month" & "IsWorkDay" and return the max date, so for each month I would then know the last working day.  How can I do this?  I tried the following, but it doesn't provide a unique date for each month, only the max date for the entire calendar:

 

kitpalmer_1-1630602488412.png

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@kitpalmer 

New column to show the last working day

 

Last Working Day = 

VAR __YEAR = 'Calendar Table'[Year]
VAR __MONTH = 'Calendar Table'[Month]
RETURN
MAXX(
    FILTER(
        'Calendar Table',
        'Calendar Table'[Year] = __YEAR && 'Calendar Table'[Month] = __MONTH && 'Calendar Table'[IsWorkDay] = TRUE()
    ),
    'Calendar Table'[Date]
)    
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@kitpalmer 

New column to show the last working day

 

Last Working Day = 

VAR __YEAR = 'Calendar Table'[Year]
VAR __MONTH = 'Calendar Table'[Month]
RETURN
MAXX(
    FILTER(
        'Calendar Table',
        'Calendar Table'[Year] = __YEAR && 'Calendar Table'[Month] = __MONTH && 'Calendar Table'[IsWorkDay] = TRUE()
    ),
    'Calendar Table'[Date]
)    
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Perfect!  Thanks for your help.

 

kp

@kitpalmer 

You are welcome !

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!