Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |