Forum Discussion

SantiagoTorres's avatar
SantiagoTorres
Helper I
3 years ago
Solved

Custom Fiscal Calendar

Hi All,

I got a custom fiscal calendar working on my report. Based on this calendar, I need to get the total workdays on my dataset [the relationship between the dates column in the data and the custom calendar is working fine]. I need help including the public holidays in my custom calendar to discount those days from the Workdays measure and the weekends.

 

The Workdays measure and the custom calendar are below. 

Workdays = COUNTROWS(SUMMARIZE('3. CoW Productivity','Calendar'[Date]))
 
Calendar =
ADDCOLUMNS (
    CALENDARAUTO ( 6 ),
    "MIndex", MONTH ( EDATE ( [Date], -6 ) ),
    "CalMonth", FORMAT ( [Date], "mmm" ),
    "CalQtr", "Q"
        & CEILING ( MONTH ( [Date] ), 3 ) / 3,
    "CalYear", YEAR ( [Date] ),
    "FinQtr", "Q"
        & CEILING ( MONTH ( EDATE ( [Date], -6 ) ), 3 ) / 3,
    "FY",
    VAR CY =
        RIGHT ( YEAR ( [Date] ), 2 )
    VAR NY =
        RIGHT ( YEAR ( [Date] ) + 1, 2 )
    VAR PY =
        RIGHT ( YEAR ( [Date] ) - 1, 2 )
    VAR FinYear =
        IF ( MONTH ( [Date] ) > 6, CY & "/" & NY, PY & "/" & CY )
    RETURN
        FinYear,
    "FinWeekNo", WEEKNUM ( EDATE ( [Date], -6 ), 2 ),
    "CalWeekNo", WEEKNUM ( [Date], 2 ),
    "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
    "Day", FORMAT ( [Date], "ddd" ),
    "STDDate", FORMAT ( [Date], "yyyymmdd" )
)

 

Finally the Public Holidays dates I need to include are,

 

AFL Grand Final FridayFriday, 24 September 2021
Melbourne Cup Day *Tuesday, 2 November 2021
Christmas DaySaturday, 25 December 2021
Boxing DaySunday, 26 December 2021
Christmas HolidayMonday, 27 December 2021
Boxing Day HolidayTuesday, 28 December 2021
COBWednesday, 29 December 2021
COBThursday, 30 December 2021
COBFriday, 31 December 2021
New Year's DaySaturday, 1 January 2022
New Year's Day (Additional)Monday, 3 January 2022
Australia DayWednesday, 26 January 2022
Labour DayMonday, 14 March 2022
Good FridayFriday, 15 April 2022
Easter SaturdaySaturday, 16 April 2022
Easter SundaySunday, 17 April 2022
Easter MondayMonday, 18 April 2022
ANZAC DayMonday, 25 April 2022
Queen's BirthdayMonday, 13 June 2022
Queen'sThursday, 22 September 2022
AFL Grand Final Friday (Tbc)Friday, 23 September 2022
Melbourne Cup DayTuesday, 1 November 2022
COBFriday, 23 December 2022
Christmas DaySunday, 25 December 2022
Boxing Day Monday, 26 December 2022
Christmas Day (additional)Tuesday, 27 December 2022
COBWednesday, 28 December 2022
COBThursday, 29 December 2022
COBFriday, 30 December 2022
New Year's DaySunday, 1 January 2023
New Year's Day (Additional)Monday, 2 January 2023
COBTuesday, 3 January 2023
COBWednesday, 4 January 2023
COBThursday, 5 January 2023
COBFriday, 6 January 2023
Australia DayThursday, 26 January 2023
Labour DayMonday, 13 March 2023
Good FridayFriday, 7 April 2023
Easter SaturdaySaturday, 8 April 2023
Easter SundaySunday, 9 April 2023
Easter MondayMonday, 10 April 2023
ANZAC DayTuesday, 25 April 2023
Queen's BirthdayMonday, 12 June 2023
AFL Grand Final Friday (Tbc)Friday, 29 September 2023
Melbourne Cup DayTuesday, 7 November 2023
Christmas DayMonday, 25 December 2023
Boxing DayTuesday, 26 December 2023
COBWednesday, 27 December 2023
COBThursday, 28 December 2023
COBFriday, 29 December 2023

 

Thanks,

 

Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi SantiagoTorres ,

     

    Assuming you have your holidays loaded in a separate table, you can modify your calendar formula to indicate whether a date is a holiday.

    Dates = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
    "Weekend/Working/Holiday",
        VAR __holiday =
            LOOKUPVALUE ( Holidays[Date], Holidays[Date], [Date] )
        RETURN
            SWITCH (
                TRUE (),
                NOT ( ISBLANK ( __holiday ) ), "Holiday",
                WEEKDAY ( [Date], 2 ) > 5, "Weekend",
                "Working"
            )
)

    DAX also has NETWORKDAYS function with an argument for holidays.

    Please see attached pbix for your reference

2 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi SantiagoTorres ,

     

    Assuming you have your holidays loaded in a separate table, you can modify your calendar formula to indicate whether a date is a holiday.

    Dates = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
    "Weekend/Working/Holiday",
        VAR __holiday =
            LOOKUPVALUE ( Holidays[Date], Holidays[Date], [Date] )
        RETURN
            SWITCH (
                TRUE (),
                NOT ( ISBLANK ( __holiday ) ), "Holiday",
                WEEKDAY ( [Date], 2 ) > 5, "Weekend",
                "Working"
            )
)

    DAX also has NETWORKDAYS function with an argument for holidays.

    Please see attached pbix for your reference