working day of year

[earter]

 I need help creating a calculated column on my calendar dimension table that tells me on each day the working day number in the year. This is so I can have parity between years on average daily sales calculations.

I can get the day number of the year, but can't figure out how to only have it count working days. I have indicators for holiday, weekend, and workingday.

Thanks for any help in advance.

[CheenuSing · Accepted solution]

Hi @earter

Please check out

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/19/working-days-computation-in-powerpivot.a...

If it work for you please accept this as solution and also give KUDOS.

Cheers

CheenuSing

[earter]

This didn't quite get me what I needed because when you span years it just keeps counting.

I adjusted the formula from Alberto's blog to this which now works as intended:

CALCULATE(SUM('Calendar'[IsWorkDay]),
FILTER('Calendar',
'Calendar'[DateKey] <= EARLIER('Calendar'[DateKey]) &&
'Calendar'[YearKey] = EARLIER('Calendar'[YearKey])))