Last 7 days =
VAR Today_ = TODAY()
VAR Difference = INT(‘Reporting Date’[Date] - Today_)
RETURN
SWITCH(TRUE(), Difference, 0, “Today”,
Difference > 2, “Last 3 days”, Difference > 4, “Last 4 days”, Difference > 5, “Last 5 Days”, Difference > 6, Difference > 7, “Last 7 Days”, “Not Applicable”)
Hello Everyone, I am trying to get my date column to show as Today, Yesterday, Last 3 days etc., but getting an error with this formula
error: “Function SWITCH does not support comparing values of types True/False with values of date type. Consider using the value/Format to convert one of the values”
@AntrikshSharma
Hello @EmmanuelBassey,
You can modify the measure as:
Last 7 days =
VAR Today_ = TODAY()
VAR Difference = INT( ‘Reporting Date’[Date] - Today_ )
RETURN
SWITCH(
TRUE(),
Difference = 0, "Today",
Difference = 1, "Yesterday",
Difference = 2, "Last 2 Days",
Difference = 3, "Last 3 Days",
Difference = 4, "Last 4 Days",
Difference = 5, "Last 5 Days",
Difference = 6, "Last 6 Days",
Difference = 7, "Last 7 Days",
"Not Applicable"
)
Regards,
Thank You @jafernandezpuga
Worked 100%
1 Like
Hello @EmmanuelBassey
I’m glad I was able to help you. The problem was the syntax of the switch and that when a condition is met, it does not continue to evaluate the next one.
Another way to do it would be:
SWITCH(
TRUE(),
OR( Difference > 7, Difference < 0 ), "Not Applicable",
Difference = 0, "Today",
Difference = 1,"Yesterday",
"Last " &Difference& " Days")
Regards,
Testing it, Today works but other criteria filter returns Empty
Hello @EmmanuelBassey ,
Can you share the example you are testing on? The tests I’m doing are working fine for me.
Regards
1 Like