Date table with custom holidays

[Feilin]

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 IsHoliday that's not working, and if I set it to FALSE it works in the return statement.

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

The Date table is a column of dates, and some derivatives in the form of calculated columns. The Days Off is just a list of dates in a single column, and there is a double 1-1 connection between them.

The weirdest part is that it USED to work, before, but then I started having issues where my Date table messed up because it was based on a CALENDARAUTO() and later CALENDAR(...), and for some reason, when it updated, it seemed ALL the calculated columns based on it messed up with no ability to undo...

First of all, how can I solve the problem of connecting the dates? Secondly (as an extra), how can I prevent my calculated columns from messing up?

[v-piga-msft · Accepted solution]

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]

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!