Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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 ) )
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)
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 ) )
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)
@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)
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |