cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
idrabble Frequent Visitor
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 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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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 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")

23.PNG

 

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

image.png

 

 



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

Proud to be a Datanaut !





idrabble Frequent Visitor
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
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 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")

23.PNG

 

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.
idrabble Frequent Visitor
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