Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heathernicole
Continued Contributor
Continued Contributor

Calculate number of days ( in a percentage) left in current month and year

I need to display the number of days left in current month and year.

 

I also need to display each of those numbers as a percentage.

 

Basically a countdown of sorts for a Sales report.

 

I already have the number of days left in month measure:

 

Workdays Left in Month = COUNTROWS(
	FILTER(
		CALENDAR(
			TODAY(),
			EOMONTH(TODAY(), 0)
		),
		WEEKDAY([Date], 2) < 6
	)
)

Any help is greatly appreciated!

~heathernicoale
2 ACCEPTED SOLUTIONS
Phil_Seamark
Employee
Employee

Hi @heathernicole

 

The change to make this a number of work days left in the year should be something like this.

 

What do you mean by displaying these numbers as a percentage?  A percentage of the total number of workdays in the Month (or year) depending on the measure used?

 

Workdays Left in Year = COUNTROWS(
	FILTER(
		CALENDAR(
			TODAY(),
			DATE(YEAR(TODAY())+1,1,1)-1
		),
		WEEKDAY([Date], 2) < 6
	)
)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Hi @heathernicole

 

This calculation returns a percentage of the number of workdays left in the year over the total number of workdays IN the year (excluding public holidays)

 

Workdays Left in Year AS Percent = 

VAR WorkdaysLeftInYear = 
	COUNTROWS(
			FILTER(
				CALENDAR(
					TODAY(),
					DATE(YEAR(TODAY())+1,1,1)-1
				),
				WEEKDAY([Date], 2) < 6
			)
		)
VAR TotalWorkDaysInYear = COUNTROWS(
			FILTER(
				CALENDAR(
					DATE(YEAR(TODAY()),1,1),
					DATE(YEAR(TODAY())+1,1,1)-1
				),
				WEEKDAY([Date], 2) < 6
			)
		)	
		
RETURN DIVIDE(WorkdaysLeftInYear,TotalWorkDaysInYear,0)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

Hi @heathernicole

 

The change to make this a number of work days left in the year should be something like this.

 

What do you mean by displaying these numbers as a percentage?  A percentage of the total number of workdays in the Month (or year) depending on the measure used?

 

Workdays Left in Year = COUNTROWS(
	FILTER(
		CALENDAR(
			TODAY(),
			DATE(YEAR(TODAY())+1,1,1)-1
		),
		WEEKDAY([Date], 2) < 6
	)
)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @heathernicole

 

This calculation returns a percentage of the number of workdays left in the year over the total number of workdays IN the year (excluding public holidays)

 

Workdays Left in Year AS Percent = 

VAR WorkdaysLeftInYear = 
	COUNTROWS(
			FILTER(
				CALENDAR(
					TODAY(),
					DATE(YEAR(TODAY())+1,1,1)-1
				),
				WEEKDAY([Date], 2) < 6
			)
		)
VAR TotalWorkDaysInYear = COUNTROWS(
			FILTER(
				CALENDAR(
					DATE(YEAR(TODAY()),1,1),
					DATE(YEAR(TODAY())+1,1,1)-1
				),
				WEEKDAY([Date], 2) < 6
			)
		)	
		
RETURN DIVIDE(WorkdaysLeftInYear,TotalWorkDaysInYear,0)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark - THANK YOU - these worked beautifully!

 

I also needed to create a percentage for the month - I modified your's - does this look accurate?

 

Workdays Left in Month AS Percent = 
	VAR WorkdaysLeftinMonth = COUNTROWS(
	FILTER(
		CALENDAR(
			TODAY(),
			EOMONTH(TODAY(), 0)
		),
		WEEKDAY([Date], 2) < 6
	)
)
VAR TotalWorkDaysInMonth = COUNTROWS(
			FILTER(
				CALENDAR(
					DATE(MONTH(TODAY()),1,1),
					DATE(MONTH(TODAY())+1,1,1)-1
				),
				WEEKDAY([Date], 2) < 6
			)
		)	
		
RETURN DIVIDE(WorkdaysLeftInMonth,TotalWorkDaysInMonth,0)
~heathernicoale

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.