cancel
Showing results for
Did you mean:
Regular Visitor

## Add column show last week and last 2 week

Hi,

I have a date column called 'DateF' that I want to create based on date values between last week and last 2 week.

If the date is in last week then value = 'Last Week'
If the date is in last 2 wweek then value = '2 Last Week'

What is the best way to do this?

I have this calculate column but dot´w works.

Last Week Sales =
IF (
Deals[DateF] <= MAX ( Deals[DateF] )
&& Deals[DateF]
> MAX ( Deals[DateF]) - 2 *7,
"2 Last Week",
IF (
Deals[DateF]
<= MAX ( Deals[DateF] ) - 1 *7
&& Deals[DateF]
>= MAX ( Deals[DateF] )- 1 * 7,
"Last Week"
)
)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Add column show last week and last 2 week

You're quite right.  The previous code was not checking the year.

Try this:

```NewColumn =
VAR _Reference =
TODAY ()
VAR _CurrentWeek =
WEEKNUM ( _Reference, 2 )
VAR _ColumnWeek =
WEEKNUM ( Deal[DateF], 2 )
RETURN
IF (
YEAR ( Deal[DateF] ) = YEAR ( _Reference ),
SWITCH (
_CurrentWeek - _ColumnWeek,
1, "Last Week",
2, "2 Last Week",
BLANK ()
)
)```

4 REPLIES 4
Super User

## Re: Add column show last week and last 2 week

Try this for a calculated column in your table. I've assumed TODAY as the base date. If this is not what you want you can update it in the variable _Reference. If DateF is neither on the last week or 2 last week, it returns blank. You can update this also in the code if required.

```NewColumn =
VAR _Reference = TODAY ()
VAR _CurrentWeek =
WEEKNUM ( _Reference, 2 )
VAR _ColumnWeek =
WEEKNUM ( Deal[DateF], 2 )
RETURN
SWITCH (
_CurrentWeek - _ColumnWeek,
1, "Last Week",
2, "2 Last Week",
BLANK ()
)```

Regular Visitor

## Re: Add column show last week and last 2 week

@AlB Thanks for the support, I have a doubt apply the measure but if I filter the column with the value "Last week" it throws me dates from 2015. My column Deal [DateF], has values ​​since 2015, how can I achieve that really just show the date of last week and 2 weeks ago. I new in  powerbi.. can u help me plz

Highlighted
Super User

## Re: Add column show last week and last 2 week

You're quite right.  The previous code was not checking the year.

Try this:

```NewColumn =
VAR _Reference =
TODAY ()
VAR _CurrentWeek =
WEEKNUM ( _Reference, 2 )
VAR _ColumnWeek =
WEEKNUM ( Deal[DateF], 2 )
RETURN
IF (
YEAR ( Deal[DateF] ) = YEAR ( _Reference ),
SWITCH (
_CurrentWeek - _ColumnWeek,
1, "Last Week",
2, "2 Last Week",
BLANK ()
)
)```

Regular Visitor

## Re: Add column show last week and last 2 week

Thx so much, works great. Thanks for ur time and help!!