Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DSwezey
Helper III
Helper III

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!




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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors