cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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(

SUM('Dimtable'[.isworkingday]),

Filter(

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

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

 

or

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

 

For more details refer to this link: https://blog.andredevelopment.com/post/power-bi-desktop-fiscal-comparison-part-1-setting-up-for-the-...

 

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.

 

October:

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.

 

WORKDAYS = SUMX (
    FILTER (
        DateTable;

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

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

DateTable[IsWorkingDay]

)

 

Need some help.

Thanks.