Need help with Time Intelligence

Power BI
1

Attached is a PBIX file that I have just about completed, but where I am having issues is developing a Measure that can count the number of remaining workdays in a selected month based on the current date. I have a measure that counts the number of workdays for the selected month, but because because I am using the month versus a date, I am struggling to calculate the remaining workdays of the selected month. I also need need to set the value to be zero workdays if the user selects a past month. Any help would be appreciated. Daily Doc.pbix (146.1 KB)

2

@lomorris,

Have to run to dinner, and haven’t had total time to check this, but give this a go:

Remaining Workdays Selected Month = 

VAR LastDay=
CALCULATE(
    COUNTROWS( Dates ),
    FILTER(
        ALL( Dates ),
        Dates[MonthInCalendar] = SELECTEDVALUE( Dates[MonthInCalendar] )
    )
)

VAR EndDate =
DATE(
    SELECTEDVALUE( Dates[Year] ),
    SELECTEDVALUE( Dates[MonthOfYear] ),
    LastDay
)

VAR WkDaysBtw =
CALCULATE(
    COUNTROWS( Dates ),
    DATESBETWEEN(
        Dates[Date],
        TODAY(),
        EndDate
    ),
    FILTER(
        Dates,
        Dates[IsWorkDay] = TRUE &&
        Dates[IsHoliday] = FALSE
    )
)

VAR Result =
IF( EndDate < TODAY(), 0, WkDaysBtw )

RETURN
Result

I hope this is helpful.

  • Brian
1 Like
3

Brian, This worked perfectly. I am reviewing each line to see how it works. Thanks again for your help.

4

@lomorris,

Happy to help – glad that got you what you needed. If you have any questions as you run through the code, just give a shout.

– Brian