Creating a Date Column With Alphabets

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

Your question made me think of this blog post/solution by Chris Webb

Chris Webb’s BI Blog: Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering And M Chris Webb’s BI Blog (crossjoin.co.uk)

Hello @EmmanuelBassey ,
Can you share the example you are testing on? The tests I’m doing are working fine for me.
Regards

1 Like