Forum Discussion

JagThripp's avatar
JagThripp
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Stachu's avatar
Stachu
Community Champion
8 years ago

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's avatar
JagThripp
Frequent Visitor
8 years ago

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's avatar
    Stachu
    Community Champion
    8 years ago

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

    • JagThripp's avatar
      JagThripp
      Frequent Visitor
      8 years ago

      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's avatar
        Stachu
        Community Champion
        8 years ago

        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