cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DSwezey
Helper II
Helper II

Calculate Billable Days to-date for current month

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.

1 ACCEPTED SOLUTION
Nathaniel_C
Super User
Super User

Hi @DSwezey  Here is a picture. You can count the days on the calendar in this. Note the year is 2004

Workdays.PNG

 


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Nathaniel_C
Super User
Super User

Hi @DSwezey  Here is a picture. You can count the days on the calendar in this. Note the year is 2004

Workdays.PNG

 


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Nathaniel_C
Super User
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors