Returning a value based on day of the week

[BachDinh]

Hi, 

I need to return a value based off the day of the week. I created two SWITCH variables as below: 

var cluster_target_weekday = 
CALCULATE(
    SWITCH(
        TRUE()
        ,cluster_type = "1", 35
        ,cluster_type = "2", 45
        ,cluster_type = "3", 55
        ,cluster_type = "4", 55
        ,cluster_type = "5", 60
        ,cluster_type = "6", 60
    )
)

var cluster_target_weekend = 
CALCULATE(
    SWITCH(
        TRUE()
        ,cluster_type = "1", 40
        ,cluster_type = "2", 50
        ,cluster_type = "3", 60
        ,cluster_type = "4", 60
        ,cluster_type = "5", 65
        ,cluster_type = "6", 70
    )
)

And now I want to populate the correct target based on the date and kiosk classification (another variable), with the sample output like this:

Cluster type	12/02/2023	13/02/2023	14/02/2023	15/02/2023
1	40	35	35	35
2	50	45	45	45
3	60	55	55	55
4	60	55	55	55

As IF(Date[Date]) within a CALCULATE function doesn't work since there's multiple dates, I am not sure what to do about this.

[PawelWrona]

Is the Date table your Calendar table? If yes, you can have a calculated column there indicating whether this is a weekday or a weekend.

[MURTAZA]

How about you create another variable to check weekend/weekday:

VAR _Weekend = IF ( WEEKDAY ( 'Date'[Date]) IN { 1, 7 }, "Weekend" , "Weekday" )

Then RETURN a combined function with _Weekend check, something like:

RETURN
cluster_target = 
    SWITCH(
        TRUE(),
        _Weekend = "Weekday" && cluster_type = "1", 35,
        _Weekend = "Weekend" && cluster_type = "1", 40,
and so on.

[BachDinh]

It doesn't allow me to use the 'Date'[Date] from the calendar table in the variable unfortunately. 

[BachDinh]

After looking at it, I think it won't allow aggregation of the calendar table for the WEEKDAY() function?

[MURTAZA]

oh right, Create a calculated column in your date table then and then. 

Weekend/Weekday = IF ( WEEKDAY ( 'Date'[Date]) IN { 1, 7 }, "Weekend" , "Weekday" )

then this measure should work

cluster_target = 
    SWITCH(
        TRUE(),
        Weekend/Weekday = "Weekday" && cluster_type = "1", 35,
        Weekend/Weekday = "Weekend" && cluster_type = "1", 40,
and so on.

I hope this works. Else upload a .pbix