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

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

  • andris_'s avatar
    andris_
    Resolver I
    9 years ago

    Hey Anonymous,

     

    yes, of course I can.

     

    Holiday table:

     

     

     

     

     

     

     

     

     

     

     

    So, these are the important columns of the Holiday table. Names can be repeated. We are refreshing it with new datas, but the old ones will still remain. So, obviously the names will be there a few times, not only once. (I think this is quite important.)

     

    Working table:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    And this is the main table with the important columns, with the names, and with the date the service orders were posted (and with the order IDs, but that's not so vital).

     

    As you see there are no unique values in it, that's why we have a standard Date(Calendar) table.

     

    Thanks and regards,

    Andris

     

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