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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KOBMP
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
d_gosbell
Super User
Super User

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
d_gosbell
Super User
Super User

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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.