Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I want to calculate the total amount of billable days (AKA: Weekdays) for the current selected month to selected date based on my slider dates.
For Example: If I had a slider set from 1/1/2021 - 3/15/2021 I would want to count all the weekdays for March 2021 up to 3/15/2021.
Within my Dates table I have a calculated column to identify weekdays vs weekends.
WeekDayStatus = IF(weekday('Date'[Date]) IN{1,7}, "Weekend", "Weekday")
I have already setup a measure to calculate how many weekdays are remaining for the current selected dates month.
Selected Date = MAX('Date'[Date])
BillableDaysRemaining =
datediff([Selected Date],EOMONTH([Selected Date],0),day) -
(calculate(
countrows('date'),
'Date'[WeekDayStatus] = "Weekend",
datesbetween('Date'[Date],[Selected Date],EOMONTH([Selected Date],0))
)
)+1
I now need to do the opposite, but get the amount of Weekdays from the beginning of the selected dates month to the selected date.
Solved! Go to Solution.
Hi @DSwezey Here is a picture. You can count the days on the calendar in this. Note the year is 2004
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @DSwezey Here is a picture. You can count the days on the calendar in this. Note the year is 2004
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @DSwezey Please try this:
Count of weekdays in latest month =
VAR _maxDate =
MAX ( 'Calendar'[Date] )
VAR _workdays =
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
ALLSELECTED ( 'Calendar' ),
MONTH ( 'Calendar'[Date] ) = MONTH ( _maxDate )
&& 'Calendar'[Weekend] = "Weekday"
)
)
RETURN
_workdays
Pictures to follow.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!