Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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"
)
)
2 ACCEPTED SOLUTIONS

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

View solution in original post

Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
AlB
Super User
Super User

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
Not applicable

@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

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
Not applicable

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 

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  Datanaut

 

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

@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 

 

SU18_powerbi_badge

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.