cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KOBMP Frequent Visitor
Frequent Visitor

Calculated Column Last Saturday of the Month

Our fiscal month end is always the last saturday of the month. I need a calculated column to derive the End of Month (EOM) date based on the transaction date column. The EOM is always the last Saturday of the month. What I can't figure out is how to apply this logic to the prior calendar dates that are after the last calendar month's Satruday. So for example:

     Date          EOM
7/22/2019   7/27/2019
6/29/2019   6/29/2019
5/26/2019   6/29/2019
5/24/2019   5/25/2019
5/24/2019   5/25/2019
4/30/2019   5/25/2019
4/25/2019   4/27/2019


Here is the excel formula I used. 

"=IF(A2>DATE(2011,12,31),IF(A2>IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)),IF(WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)=7,EOMONTH(EOMONTH(A2,0),1),EOMONTH(EOMONTH(A2,0),1)-WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)),IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1))),EOMONTH(A2,0))"

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculated Column Last Saturday of the Month

So DAX has all of those functions you used in your Excel formula. Therefore you could created a calculated column like the following (you might just need to change the table name in the second line)

EOM2 = 
VAR A2 = 'Table'[Date]
RETURN IF(A2>DATE(2011,12,31),IF(A2>IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)),IF(WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)=7,EOMONTH(EOMONTH(A2,0),1),EOMONTH(EOMONTH(A2,0),1)-WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)),IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1))),EOMONTH(A2,0))

View solution in original post

1 REPLY 1
Super User
Super User

Re: Calculated Column Last Saturday of the Month

So DAX has all of those functions you used in your Excel formula. Therefore you could created a calculated column like the following (you might just need to change the table name in the second line)

EOM2 = 
VAR A2 = 'Table'[Date]
RETURN IF(A2>DATE(2011,12,31),IF(A2>IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)),IF(WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)=7,EOMONTH(EOMONTH(A2,0),1),EOMONTH(EOMONTH(A2,0),1)-WEEKDAY(EOMONTH(EOMONTH(A2,0),1),1)),IF(WEEKDAY(EOMONTH(A2,0),1)=7,EOMONTH(A2,0),EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1))),EOMONTH(A2,0))

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 318 members 2,901 guests
Please welcome our newest community members: