Forum Discussion
Measure\Column calculations not correct
This is my second posting related to getting a aged report for duration of open tickets.
I managed to get something sort of working by advise from this forum, but alas there appears to be errors in my application of those recommendations.
I have a holidays table with all of the relevant localized public holidays present, which I am using as a reference table.
I have created a calculated column and a measure, but when they are calculated the values returned are not correct.
I validated this by running VBA code over the date fields in Excel.
Below are the fields that I am using, and the results column.
Here is the results from the Excel Calculations, as you can see there is considerable difference between the two.
Also why would there be blank rows in the coulmn?
The Columns DAX:
Total Working Days Column = SUMX(FILTER('Holidays','Holidays'[Dates] >= 'ActiveWork_Mashup'[Created] && 'Holidays'[Dates] <= if(ISBLANK('ActiveWork_Mashup'[Closed]),TODAY(),'ActiveWork_Mashup'[Closed])),'Holidays'[Dates].[Day])
The Measure:
Total Working Days Measure = SUMX(FILTER('Holidays','Holidays'[Dates].[Day] >= MIN('ActiveWork_Mashup'[Created]) && 'Holidays'[Dates].[Day] <= MAX('ActiveWork_Mashup'[Closed])),'Holidays'[Dates].[Day])
Thank you all in advance, this is helping my learning curve tremendously.
Jag
Hi JagThripp,
Maybe you can try to use below formula.
Sample formula of calculate column:
Available workdays = VAR workdayList = FILTER ( CALENDAR ( Table[Created], Table[Updated] ), WEEKDAY ( [Date], 2 ) <= 5 ) //remove weekend VAR holidayList = VALUES ( Holiday[date] ) //holiday list VAR remain = EXCEPT ( workdayList, holidayList ) //workday except holiday RETURN COUNTROWS ( remain )If above not help, can you please share some sample data and holiday table? I will try to coding formula on it.
Regards,
Xiaoxin Sheng
7 Replies
not sure the StartDay and EndDay are OK, but hopefully it will be easy to adjust. it assumes that
1) the holidays table is including both weekends and bank holidays
2) Closed date is always later than Opened date= VAR StartDay = 'ActiveWork_Mashup'[Opened] VAR EndDay = IF(ISBLANK('ActiveWork_Mashup'[Closed]),TODAY(),'ActiveWork_Mashup'[Closed]) VAR NrOfAllDays = DATEDIFF(StartDay;EndDay;DAY) VAR NrOfHolidays = COUNTROWS(FILTER(ALL('Holidays'),AND(Holidays[Dates]<=EndDay,Holidays[Dates]>=StartDay))) RETURN NrOfAllDays - NrOfHolidaysEDIT - added ALL for 'Holidays' in case there is relationshop between the tables
