Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating Working Days Left in Month excluding holidays

I've seen a few other somewhat similar threads to calculate this using DAX, but nothing including how to exclude specific holidays. 

I have a separate date table that has "Weekday/Weekend" column, and a "Holiday/Not a Holiday" column. 

I currently have a measure that counts the total number of working days. 

Ship_Days_Total = CALCULATE(COUNTROWS(
'Calendar'),
FILTER('Calendar', 'Calendar'[WeekdayWeekend] = "Weekday"),
FILTER('Calendar','Calendar'[Holiday_Flag] = "Not a Holiday"))
I'm then using relative date filtering to just show the max possible working days in a month as of now. But, we set a sales target every month, and they want a KPI that shows dynamically what we need to average in sales each day to hit that target. 

I've seen another DAX formula from this forum for calcuating the number of weekdays left in a month: 

Workdays Left in Month = COUNTROWS(
	FILTER(
		CALENDAR(
			TODAY(),
			EOMONTH(TODAY(), 0)
		),
		WEEKDAY([Date], 2) < 6
	)
)
But how would I calculate the number of days left in the month, including weekdays, but EXCLUDING holidays as marked in my calendar table? Any help is greatly appreciated. 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Something like:

     

    Workdays Left in Month = COUNTROWS(
    EXCEPT(
	FILTER(
		CALENDAR(
			TODAY(),
			EOMONTH(TODAY(), 0)
		),
		WEEKDAY([Date], 2) < 6
	),
        SELECTCOLUMNS(
          FILTER(
               'Dates',[IsHoliday] = 1
          ),
          "Date",[Date]
        )
)

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Something like:

     

    Workdays Left in Month = COUNTROWS(
    EXCEPT(
	FILTER(
		CALENDAR(
			TODAY(),
			EOMONTH(TODAY(), 0)
		),
		WEEKDAY([Date], 2) < 6
	),
        SELECTCOLUMNS(
          FILTER(
               'Dates',[IsHoliday] = 1
          ),
          "Date",[Date]
        )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much for this. Once I corrected the error on my end it all worked out. I've just never really worked with the EXCEPT function before. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous No worries, I wasn't trying to yell at you BTW, I was just trying to add emphasis. I will remember to use bold next time!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler When I attempt to write that desk as pertinent into my report, I run into an issue after the SELECTCOLUMNS / FILTER portion after we account for holidays. 

      You have
      "Date",[DATE]  but it's giving me errors when I attempt to enter this last part in. 

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi  Anonymous 

    For your case, I would suggest you use this simple formula to create a measure

    Measure = 
COUNTROWS (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= TODAY ()
            && 'Calendar'[Date] <= EOMONTH ( TODAY (), 0 )
            && 'Calendar'[Holiday_Flag] = "Not a Holiday"
            && WEEKDAY ( 'Calendar'[Date], 2 ) < 6
    )
)

     

    If you still have the problem please share your sample pbix file and your expected output.

     

    Regards,

    Lin