cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GerardoArevalo Regular Visitor
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
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
Super User

Re: Add column show last week and last 2 week

Hi @GerardoArevalo 

 

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 ()
    )

 

GerardoArevalo Regular Visitor
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
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 ()
        )
    )

 

GerardoArevalo Regular Visitor
Regular Visitor

Re: Add column show last week and last 2 week

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