Forum Discussion

Rocky_Brown's avatar
Rocky_Brown
Helper I
3 years ago
Solved

Switch Comparing 2 dates

I have 2 date tables both with unique Dates.

1. Is a Table that displays Year and Good Friday Date

     Year             Good Friday

     2021            04/02/2021

     2022            04/15/2022

     2023            04/07/2023 

2, The other is a general Date table.

 

I am trying to create a "Holiday" column in the the Date table using "Switch"

Holiday =
Switch(
True(),
CALCULATETABLE(VALUES('Good Friday Years'[Good Friday]),NOT(ISBLANK('Good Friday Years'[Good Friday]))) = CALCULATETABLE(VALUES('Date'[Date]),NOT(ISBLANK('Date'[Date]))), "Good Friday",
""
)
 
I keep getting the error "A table of multiple values was supplied where a single value was expected.
 
How do I compare the date to the good friday date in order to populate the Holdiay column?
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    Rocky_Brown 

    Try someting like this for the column.

    Holiday =
SWITCH (
    TRUE (),
    NOT ISBLANK (
        LOOKUPVALUE (
            'Good Friday Years'[Good Friday],
            'Good Friday Years'[Good Friday], Dates[Date]
        )
    ), "Good Friday",
    ""
)

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    Rocky_Brown 

    Try someting like this for the column.

    Holiday =
SWITCH (
    TRUE (),
    NOT ISBLANK (
        LOOKUPVALUE (
            'Good Friday Years'[Good Friday],
            'Good Friday Years'[Good Friday], Dates[Date]
        )
    ), "Good Friday",
    ""
)
  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    You can try something like...

    Holiday = 
    var _goodFriday =
    LOOKUPVALUE('Good Friday Years'[Good Friday], 'Good Friday Years'[Year], dimDate[Date].[Year], 'Good Friday Years'[Good Friday], dimDate[Date])
    return
    IF(
        ISBLANK(_goodFriday),
        "",
        "Good Friday"
    )

     

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    3 years ago

    I have attached a file with a Dates table along with a Holiday table and a GoodFriday table and I use both of those to tag the holidays on the dates table.  The idea for the holiday table came from this post

    https://community.powerbi.com/t5/Desktop/Holiday-date-patterns/m-p/768950

    It uses the day of the week as well as the day of the month of the holiday and the month to identify what dates should be tagged as holidays.