Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    4 years ago

    Hi Anonymous  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

2 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 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

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous  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