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