Forum Discussion

andris_'s avatar
andris_
Resolver I
9 years ago
Solved

date/holiday issue

Hey everyone,

 

I'm quite new to the Power BI, I've just started working with it since January. We are trying to automate some processes, and there's a problem with it when it comes to the personal holidays. We've been trying to solve it from about a week, and we just can't work it out.

 

The point of the report is to measure the daily performances of our technicians. It also aggregates the daily performances monthly.

 

The holiday table of our associates is from a report, and besides some other columns (Year, number of days, claimant etc.), there's a column with the starting date of the claimant's holiday, and there's a column with the last day of the claimant's holiday.

 

We have a report from an ERP which contains the service orders in pairs with the technicians and with other datas. Of course, if someone does not work on a day (because he's on holiday), the report does not create a service order to that day for that technician. But (and that is our main problem) if the technician orders something on his phone, the program generates a service order. And it ruins his work time utilization, because it is calculated from the effective work hours (which is about 2 mins in this precedent) divided with the total work hours (8 hours).

 

So, to put it in a nutshell, we'd like to create a measure (?) which checks that a person's (service order) posting date is in the range of his holiday (which is in the table I've mentioned in the 3rd paragraph). We can not connect the two tables direct, because there's no unique values in it (but it is connected indirectly with a Date table).

 

I know it's not so simple, so I tried to describe it detailed, but I'm not sure if it's crystal clear, so please ask me if something is not evident.

 

Thanks in advance, and have a nice Friday/weekend,

 

Andris

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi andris_,

     

    You can try to use belwo formulas:

    1. Add a calculated column to holiday table to convert the holiday date range to text.

     

    HolidayRange = CONCATENATEX(CALENDAR([From],[Till]),[Date],",")

     

    2. Add a measure to working table to calculate if the order is posting in the holiday range.

     

    IsPostInHoliday = 
var currDate=MAX(Working[Date])
Var currUser= LASTNONBLANK(Working[Technican name],[Technican name])
var totalHoliday= CONCATENATEX(FILTER(ALLSELECTED(Holiday),[Claimant]=currUser),[HolidayRange],",") //summary all date range
return
ISERROR(Search(currDate,totalHoliday))=FALSE()

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi andris_,

     

    You can simply use INTERSECT function to check the posting date which in the holiday range

     

    Table formula:

     

    posting holiday= INTERSECT(VALUES('Working Table'[Posting Date]),VALUES('Holiday Table'[Date]))

     

    Add a measure to check the CALENDAR table.

     

    Work in Holiday = CONTAINS('Possting holiday','Possting holiday'[Date],MAX(Calendar[Date]))

     

    Regards,

    Xiaoxin Sheng

    • andris_'s avatar
      andris_
      Resolver I
      9 years ago

      Hi Anonymous,

       

      First of all, thanks for your answer!:smileyhappy:

       

      Secondly, that INTERSECT function seems fine to me, I haven't heard of it before! But, I don't get, how will the table formula work that you mentioned. What's the second value, the 'Holiday Table'[Date]? We have 2 colums of the holiday dates in the table, a starting date and a finishing date.

      Or, have I misunderstood something?

       

      Thanks,

      Andris Szepesy

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi andris_,

         

        Can you please share some sample data?

         

        Regard,

        Xiaoxin sheng