Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Goal: To caulcate "Average pace per day needed to hit goal"
I built a Federal Holiday Table and created relationship between it and my "Master Date" Table.
Next I calculated: Total work days = CALCULATE(COUNT('Master Date'[Master Date].[Date]),FILTER('Master Date','Master Date'[If work day]=1))
What i need now is "Remaining business days (M-F No Holidays), excluding today left in this month.
I tried:
Work Day Left = COUNTROWS(FILTER(CALENDAR(TODAY(),EOMONTH(TODAY(),0)),WEEKDAY([Date],2)<6)) |
but it resulted in "18". Today is the 8th, so excluding today and Memorial day there are 16 business days remaining.
I just can't seem to get it right! Help please!
Solved! Go to Solution.
This measure works.
Remaining Workdays = VAR LastDayOfCurrentMonth = CALCULATE( ENDOFMONTH(Dates[Date]), FILTER(Dates,Dates[Month] = MONTH(TODAY())) ) Return CALCULATE( COUNTROWS(Dates), FILTER(Dates,Dates[Date] >= TODAY() && Dates[Date] <= LastDayOfCurrentMonth), FILTER(Dates,Dates[WorkDay] = TRUE()) ) -1
There might be an shorter way to do this, but I used a true Date table, so I needed it to figure out the last day of the current month regardless of the filter context, so that is what the LastDayOfCurrentMonth variable is figuring out.
My date table also has the Workday field as a logical true/false, not 1,0, but you could change my last FILTER() statement to be = 1 vs = TRUE().
The '-1' at the end is because you didn't want to include today. It calculated 15 for me today, which is correct since today is the 9th. 31 - 9 = 22 - 6 weekend days = 16 - 1 Memorial Day holiday = 15.
If you want to take a look at my PBIX file it is here. You would just relate the date in my date table to the date in your data table, and it would still work. I used a public holiday calendar, to the number of holidays is unrealistic for most companies, but that can be edited on your side for your particular company.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis measure works.
Remaining Workdays = VAR LastDayOfCurrentMonth = CALCULATE( ENDOFMONTH(Dates[Date]), FILTER(Dates,Dates[Month] = MONTH(TODAY())) ) Return CALCULATE( COUNTROWS(Dates), FILTER(Dates,Dates[Date] >= TODAY() && Dates[Date] <= LastDayOfCurrentMonth), FILTER(Dates,Dates[WorkDay] = TRUE()) ) -1
There might be an shorter way to do this, but I used a true Date table, so I needed it to figure out the last day of the current month regardless of the filter context, so that is what the LastDayOfCurrentMonth variable is figuring out.
My date table also has the Workday field as a logical true/false, not 1,0, but you could change my last FILTER() statement to be = 1 vs = TRUE().
The '-1' at the end is because you didn't want to include today. It calculated 15 for me today, which is correct since today is the 9th. 31 - 9 = 22 - 6 weekend days = 16 - 1 Memorial Day holiday = 15.
If you want to take a look at my PBIX file it is here. You would just relate the date in my date table to the date in your data table, and it would still work. I used a public holiday calendar, to the number of holidays is unrealistic for most companies, but that can be edited on your side for your particular company.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI had to make a few tweaks to my table but this totally worked! Thank you so much!
User | Count |
---|---|
89 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |