Calculated column for dates isn't working

[Anonymous]

I've got a table with the total task backlog and I'm trying to separate out what is current, last week, last week in previous month and historical. 

I currently have;

Date Result = Switch(

true(),

'Combined Evolving Backlog'[Report Date]<[Latest Date Previous Month],"Historical",

'Combined Evolving Backlog'[Report Date]=[Latest Date Previous Month],"Last Month",

'Combined Evolving Backlog'[Report Date]=[Previous],"Last Week",

'Combined Evolving Backlog'[Report Date]>=[Latest Report],"Latest")

Where Latest Date previous month, previous and latest report are measures. 

What am I doing wrong? The results currently only give Last month and Last week. 

[amitchandak]

@Anonymous , Try something like this

Week Type =
var _st = today() +-1*WEEKDAY(today() ,2)+1
var _end = today() + 7-1*WEEKDAY(today(),2)
return
Switch( True(),
[Date] >= _st && [Date] <= _end ,"This Week" ,
[Date] >= _st-7 && [Date] <= _end -7,"Last Week" ,
[Week Name]
)

Modify as per need

[Anonymous]

Thank you so much!

This works perfectly for this week and last week, how do I modify to pick up the last date of the previous month and return "Last Month"?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can try function 'EOMONTH' to pick up the last date of the previous month, please modified the formula as below:

Type = 
VAR _weekstart =
    TODAY ()
        + -1 * WEEKDAY ( TODAY (), 2 ) + 1
VAR _weekend =
    TODAY () + 7
        - 1 * WEEKDAY ( TODAY (), 2 )
VAR _lastmonthstart =
    EOMONTH ( TODAY (), -2 ) + 1
VAR _lastmonthend =
    EOMONTH ( TODAY (), -1 )
RETURN
    SWITCH (
        TRUE (),
        [Report Date] <= _lastmonthstart, "Historical",
        [Report Date] >= _lastmonthstart
            && [Report Date] <= _lastmonthend, "Last month",
        [Report Date] >= _weekstart
            && [Report Date] <= _weekend, "This Week",
        [Report Date] >= _weekstart - 7
            && [Report Date] <= _weekend - 7, "Last Week"
    )

Please check my attachment (may need refresh for latest results).

Best Regards,
Community Support Team _ Eason