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.

View solution in original post

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? 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.

View solution in original post

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 59 members 1,249 guests
Please welcome our newest community members: