Add column show last week and last 2 week

[Anonymous]

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"

)

)

[AlB · Accepted solution]

You're absolutely right. The code above wasn't checking the year.

Try this:

NewColumn á
VAR _Reference - HOY
    () 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 ()
    )

[AlB]

Hi @Anonymous 

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

[Anonymous]

@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

[AlB · Accepted solution]

You're absolutely right. The code above wasn't checking the year.

Try this:

NewColumn á
VAR _Reference - HOY
    () 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 ()
    )

[Anonymous]

Hi @AlB 

I have the same problem but I need to get This week, Last Week, and Last Last Week. Do you have a solution for this? Thank a lot!

Min Li 

[AlB]

Hi @Anonymous 

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",
            3, "3 Last Week"
            BLANK ()
        )
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[AnalystDF]

This works but how can I include this week for this column as well? 

[AlB]

@AnalystDF 

I don't understand the question

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous · Accepted solution]

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