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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SudipChoudhury
Regular Visitor

Slicer with concatenated value of string and calculated column is not showing correct value

I have a date table in that there is one Period column and a week column. So I have created a calculated column as Period_date so getting the values as P01W1, P02W2 etc. I have show 5 weeks Period_weeks(last two weeks, current week & future two weeks) in a slicer. For example if Current week is P05W4, then in slicer I have to show P05W2,P05W3,P05W4,P06W1,P06W2. So I created one calculated column to give rank to the weeks. 

Period_Week_Rank = DATEDIFF('Dim_Time_Wk'[SALE_TKT_WK_BEGIN_DT],TODAY(),WEEK).
When I am placing the Period_week column in a slicer then from filter section, I am selecting the Rank values as -2,-1,0,1,2. That way I am getting the 5 weeks in slicer properly.
But the problem is that, as per the requirement, for Current week, I have to show it like P05W4 (Current Week).
So for this, I created another calculated column and wrote the function as 
Pd_Wk_Test = IF(Dim_Time_Wk[Period_Week_Rank]=0,Dim_Time_Wk[Period_Week]&" (Current Week)",Dim_Time_Wk[Period_Week])
Every Sunday, the week starts. So I expected that this concatenated text will be changed on every sunday based on the change in current week by following the condition Period_week_rank = 0. But unformtunately the text Current Week gets added to the new week on every sunday but it also stays on last week. For example - if current week is P05W4 then in slicer it is showing  P05W4 (Current week). Now in coming Sunday, it should be showing P06W1(Current Week) in slicer. But what happens is, it is going to show P05W4 (Current Week) as well as P06W1 (Current Week) in slicer.
Is there any way to resolve this issue. I need to show "PxWx (Current Week)" only for Current Period_Week and rest should be showing in the format "PxWx".

SudipChoudhury_2-1684230033366.png

This is an issue I got in one week. The text Current Week should have shown only for P05W4. but it was showing for two weeks in slicer.

SudipChoudhury_3-1684230070205.png

 

3 REPLIES 3
SudipChoudhury
Regular Visitor

Someone pls help. I am still facing this issue. Every Sunday it is showing the Current week for two weeks.

@SudipChoudhury for a period week you have to take a call. You have to use start of week or end of week and complete the week will in that period.

 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)

 

use one of these to calculate period week

 

for comparing this week vs last week

you can have one more column

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)

 

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

@amitchandak .. I don't need to create Period Week column. I already have period week column. I have to show 5 weeks of Period week names in slicer. 2 prior weeks, Current Week and 2 future weeks. And I have to show it like this:

SudipChoudhury_0-1684503291598.png

 

 I am creating two calculated column like this to add that text "Current Week" for current week.

Period_Week_Rank = DATEDIFF('Dim_Time_Wk'[SALE_TKT_WK_BEGIN_DT],TODAY(),WEEK).

Pd_Wk_Test = IF(Dim_Time_Wk[Period_Week_Rank]=0,Dim_Time_Wk[Period_Week]&" (Current Week)",Dim_Time_Wk[Period_Week])

Now when I am placing this Pd_Wk_Test in slicer, every Sunday when week starts the text "Current Week" remains for past week. It gets added to the Current week but it also remains added in past week. Like below case happened in one Sunday:

SudipChoudhury_3-1684503570592.png

Is there any way, this issue can be fixed? I want it like every Sunday that text Current Week get added to the new week but that should be stay added for past weeks.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.