Measure\Column calculations not correct

[JagThripp]

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

[Stachu]

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 - NrOfHolidays

 EDIT - added ALL for 'Holidays' in case there is relationshop between the tables

[JagThripp]

Hi There Stachu,

thank you for responding to my query.

Now down to the nitty gritty.

How and where do I apply this soloution, remembering I am more than a newbie here?

[Stachu]

this is calculated column formula, so you should put it in 'ActiveWork_Mashup' table

[JagThripp]

added as a column, now the dates are in all rows, but still wrong, it is almost like it is not taking into consideration weekends and holidays.

Take into consideration the 08/05/18 - 14/05/18 date range that has 4 days calculated in Excel (yesterday).

Just doing a date to date in Excel shows that there should be 5 days, the new column is returning 7 which is everything including weekends.

Tuesday, 8 May 2018

Wednesday, 9 May 2018

Thursday, 10 May 2018

Friday, 11 May 2018

Saturday, 12 May 2018

Sunday, 13 May 2018

Monday, 14 May 2018

[Stachu]

as I said earlier the 2) assumption is that holidays table contains dates for weekends
you can work around it with this

=
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) || WEEKDAY(Holidays[Dates],3)=6 || WEEKDAY(Holidays[Dates],3)=7))
RETURN
NrOfAllDays - NrOfHolidays

[JagThripp]

Hi Stachu,

I appreciate your patience with me on this.

I have one more question, is it necessasry to have every day of the week listed in the Holidays table, indicating actual holidays?

Or will the DAX you have provided count all week days and exclude holidays?

I must ask this as the latest snipet you have provided still is not calculating the number of days between the CREATED date and NOW.

I redid the calculation in Excel this morning so the image on the right is what I would expect to see in Power BI.

The image on the left "COLUMN" is the new calculation provided by your good self.

To be honest I would rather not have to add all the dates into the HOLIDAYS table.

[v-shex-msft · Accepted solution]

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