Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 () )
Thx so much, works great. Thanks for ur time and help!!
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 () )
@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 () )
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
This works but how can I include this week for this column as well?
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
Thx so much, works great. Thanks for ur time and help!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |