End of Quarter minus 2 work days

[idrabble]

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 Calendar2 = ADDCOLUMNS(Calendar1,"Quarter",ROUNDUP(MONTH([Date])/3,0))
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))
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?

Thanks in advance, Ian

[v-frfei-msft · Accepted solution]

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 Calendar2 = ADDCOLUMNS(Calendar1,"Quarter",ROUNDUP(MONTH([Date])/3,0))
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2))
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

[idrabble]

Thanks Frank,

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

Regards, Ian

[PattemManohar]

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

[idrabble]

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

Regards, Ian