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

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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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