cancel
Showing results for
Did you mean:
Frequent Visitor

## End of Quarter minus 2 work days

Hello All,

For a report for our sales team I am trying to show the last date of sales for invoicing. Within our company this is the third to last workday, therefore I am trying to find the end of the Quarter minus 2 work days. This is my measure so far:

End of Quarter =
VAR TodaysDate = Today()
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR TodaysMonth = ROUNDUP(MONTH(TodaysDate)/3,0)
VAR Calendar4 = FILTER(Calendar3,[Quarter]=TodaysMonth&&[WeekDay1]<6)
VAR LastWorkingDay = MAXX(Calendar4,[Date])-2
RETURN FORMAT(LastWorkingDay,"dd/mm/yy")

Unfortunately this gives the last day as 29th Dec and this is a Saturday, im this example I am looking for this to be the 27th.

I believe my LastWorkday variable is the issue, I need to subtract 2 work days, not just 2 days.

Any thoughts?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: End of Quarter minus 2 work days

Hi @idrabble,

To update your measure as below.

```End of Quarter =
VAR TodaysDate = Today()
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR TodaysMonth = ROUNDUP(MONTH(TodaysDate)/3,0)
VAR Calendar4 = FILTER(Calendar3,[Quarter]=TodaysMonth&&[WeekDay1]<6)
VAR LastWorkingDay = TOPN(3,Calendar4,[Date],DESC)
var las = MINX(LastWorkingDay,[Date])
RETURN FORMAT(las,"dd/mm/yy")```

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

## Re: End of Quarter minus 2 work days

@idrabble Please try to use below (assuming that you have calendar table in place already as mentioned in below post)

Create a new table as

`Test139Out = GROUPBY(FILTER(Test139EOQ,NOT [WeekDayNo] IN {6,7}),Test139EOQ[Quarter],"LastDay",MAXx(CURRENTGROUP(),Test139EOQ[Date]))`

Then add a calculated column (which is the expected output) as below

```Final =
IF (WEEKDAY(Test139Out[LastDay],2)=1,Test139Out[LastDay]-4,Test139Out[LastDay]-2)```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Frequent Visitor

## Re: End of Quarter minus 2 work days

Thanks @PattemManohar but I am really looking to answer this in a measure, not create another table

Regards, Ian

Community Support Team

## Re: End of Quarter minus 2 work days

Hi @idrabble,

To update your measure as below.

```End of Quarter =
VAR TodaysDate = Today()
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR TodaysMonth = ROUNDUP(MONTH(TodaysDate)/3,0)
VAR Calendar4 = FILTER(Calendar3,[Quarter]=TodaysMonth&&[WeekDay1]<6)
VAR LastWorkingDay = TOPN(3,Calendar4,[Date],DESC)
var las = MINX(LastWorkingDay,[Date])
RETURN FORMAT(las,"dd/mm/yy")```

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: End of Quarter minus 2 work days

Thanks Frank,

I was so close but you've got me over the line

Regards, Ian

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 59 members 1,249 guests
Recent signins: