Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Rocky_Brown
Helper I
Helper I

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?
1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@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",
    ""
)

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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.

jdbuchanan71_0-1671719238165.png

 

 

 

jgeddes
Super User
Super User

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

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





jdbuchanan71
Super User
Super User

@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",
    ""
)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.