Showing results for 
Search instead for 
Did you mean: 
JagThripp Frequent Visitor
Frequent Visitor

Re: Number of working days

Hello There.


Calendar = CALENDAR(DATE(2012,01,01),DATE(2020,12,31)) - CREATED FINE


IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1) - ERROR = Cannot find table 'Calendar',


This looks to be exactly what I need, I just need to get the measures working.

JagThripp Frequent Visitor
Frequent Visitor

Re: Number of working days

Hi There.


Calendar = CALENDAR(DATE(2012,01,01),DATE(2020,12,31)) - Created fine


IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1) - Error on "Cannot find table 'Calendar'.


This procedure looks perfect for my needs just rying to get it to work.


Thanks for posting.

Re: Number of working days

I need to follow this post as I am now having the same issue with one of my Power BI reports.  I have a starting date (creation) and an ending date (response) and I need to count the number of WORKING DAYS (minus a possible holiday).  I tried to follow the same example and created a measure of Calendar = CALENDAR(DATE(2012,01,01),DATE(2020,12,31)) and then
IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1), but I get the same error about 'Calendar' not found.  I am thinking that Calendar needs to be a table by itself?


I did some testing and yes, create a NEW TABLE using that first DAX and then IsWorkingDay would be a measure to that 'Calendar' table.  Then the OverallTAT DAX formula is for your main table.  Now I need to figure in weeday holidays and then I will be golden.

Archie1 Frequent Visitor
Frequent Visitor

Re: Number of working days

Hi @jleibowitz,


I am not sure if you have got your answer or not.

Here is the answer, output for below formula will be either 1 or 0.


If you are planning to get total no. of days then you have to use COUNTROWS or SUM function in your main field.


eg: Nofworkingdays_Month1= CALCULATE(



All(Dimtablename), 'dimtable[date].[MonthNo] = Month(MAX(newtable[date]))

&&  'dimtable[date].[Year] = YEAR(MAX(newtable[date])) ))



Nofworkingdays_Month2 = CALCULATE(COUNTROWS('Lastmonth'), Dimtable[.IsworkingDay] =1)


For more details refer to this link:


Cheers, Archie

marcionlinerj Frequent Visitor
Frequent Visitor

Re: Number of working days

I got a situation that I have to set another filter to the function and I don't know how to do that.
I need to get the work days from CalendarTable but just the last 15 days(I call it "cycles") of the last month from Table1.

So the months have 2 cycles and each cycle has an ID.



cycle 1: TableDate[CycleID] = 391, Table1[start_date] = 01/10/2018 (DD/MM/YYYY) , Table1[end_date] = (15/10/2018)

cycle 2: TableDate[CycleID] = 392, Table1[start_date] = 16/10/2018 (DD/MM/YYYY) , Table1[end_date] = (31/10/2018) *last day depends on the month


I created a Switch formula in calendarTable to flag the cycles, a relationship with a table of IDs and it's ok.


If I put the formula into a table visual with the cycles on columns it works, but I want the number of work days just for the last cycle in a Visual Card that shows just 1 value.


    FILTER (

        DateTable[Date] >= MIN(Table1[START_DATE])

        && DateTable[Date] <= MAX(Table1[END_DATE]) 
        && DateTable[CycleID] = MAX(Table1[CYCLEID])); => it doesn't work




Need some help.