cancel
Showing results for
Did you mean:
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

Accepted Solutions 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))``` 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))```

Announcements #### 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. #### Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones. #### 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

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 318 members 2,901 guests
Recent signins:
• wjkolesar • Jorgast • Jotad710 • • forbes_djohnson • joshuabrock • MattBossert • svanvalk • jaehchang • smcalister • rray9895 • 1Watsonj • ryanchynoweth • arshkaur93 