Forum Discussion

Feilin's avatar
Feilin
Icon for Helper II rankHelper II
8 years ago
Solved

Date table with custom holidays

I have a problem implementing a date table with holidays. I want to check if a date is a holiday or sunday, set work time to 0, for saturdays 0,25 and for normal work days 1. I already know it's IsHo...
Need Help
Show and Tell
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi Feilin,

     

    I have made a test with a Days off table containing holiday dates according to your description, everything works as excepted.

    I already know it's IsHoliday that's not working, and if I set it to FALSE it works in the return statement

    Could you show the screenshots about your this issue?

     

    I created a calculated column with the formula below.

     

     

    Work Time = 
VAR IsHoliday =
    CALCULATE (
        COUNTROWS ( 'Days Off' ),
        FILTER ( 'Days Off', 'Days Off'[Date] = 'Date'[Date] )
    )>0
VAR Weekday =
    WEEKDAY ( 'Date'[Date], 2 )
RETURN
    IF ( OR ( IsHoliday, Weekday = 7 ),0, IF ( Weekday = 6, 1 / 4, 1 )

     

    Here is my test result.

     

     

    I also have a test with changing the CALENDARAUTO() table to CALENDAR(...),but I don't have any error. 

     

    The details you could refer to the test pbix attached below.

     

    If you still have problems, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi Feilin,

 

I have made a test with a Days off table containing holiday dates according to your description, everything works as excepted.

I already know it's IsHoliday that's not working, and if I set it to FALSE it works in the return statement

Could you show the screenshots about your this issue?

 

I created a calculated column with the formula below.

 

 

Work Time = 
VAR IsHoliday =
    CALCULATE (
        COUNTROWS ( 'Days Off' ),
        FILTER ( 'Days Off', 'Days Off'[Date] = 'Date'[Date] )
    )>0
VAR Weekday =
    WEEKDAY ( 'Date'[Date], 2 )
RETURN
    IF ( OR ( IsHoliday, Weekday = 7 ),0, IF ( Weekday = 6, 1 / 4, 1 )

 

Here is my test result.

 

 

I also have a test with changing the CALENDARAUTO() table to CALENDAR(...),but I don't have any error. 

 

The details you could refer to the test pbix attached below.

 

If you still have problems, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Feilin's avatar
Feilin
Icon for Helper II rankHelper II
8 years ago

Thank you very much for your help!

 

I restructured my date table in the query editor, and now it works like a charm!

 

I don't know if the problem was because I was creating a calculated table and then expanding it with calculated columns that somehow were calculated in a weird order that messed it up once it updated or something. I think that the original date column didn't even appear anymore, once I changed the formula (I tried going from CalendarAuto() to Calendar()).

 

But, either way, now it works, so thanks again!