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.
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.
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.
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: https://blog.andredevelopment.com/post/power-bi-desktop-fiscal-comparison-part-1-setting-up-for-the-...
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.
WORKDAYS = SUMX (
DateTable[Date] >= MIN(Table1[START_DATE])
&& DateTable[Date] <= MAX(Table1[END_DATE])
&& DateTable[CycleID] = MAX(Table1[CYCLEID])); => it doesn't work
Need some help.