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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Dynamic 13 weeks based on baseweek selection slicer

Hi All, I am new to Power BI.

 

Can anyone please help me on dynamic 13 week selection based on my base week slicer.

 

Eg, if i select 202013 then i need the output as 202001 to 202013

 

if i select 202014 then i need the output as 202002 to 202013

 

My slicer is FYWK from below image.

 

 

Capture.PNG 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Icey  I have used Lookupvalue and hasonevalue to get my requirement. 

 

Below are the measures which i have used. 

 

13 WKS = 

CALCULATE(SUM('TM Trend'[Sat_Day_Test_Count]), DATESBETWEEN('calendar'[Reference_Date], [CUS_CY13W_Start], [CUS_CY_BaseDate]))
 
CUS_CY13W_Start = LOOKUPVALUE('Calendar'[CUS_CY13W_StartDate], 'Calendar'[CUS_CY_Baseweek], [CUS_CY_BaseWeek_Selected])
 
CUS_CY_BaseWeek_Selected = IF(HASONEVALUE('Weeklist'[Base Week]), VALUES('Weeklist'[Base Week]), BLANK())

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi I have the following scenario, as you said i have achived the measures for the past 13 week.
Based on the date selected my measures will show low/high/avg of call time through measure.
In the same report i need to show a line chart which should show the past 13 weeks trend like based on the date selected.
Could please let me know how to do this?
Dynamically changing calculated columns are not available in power bi. also measure will return only one value. but in my case if a date is selected i need to show the past 13 week in trend like.

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please let us know whether this problem has been solved.

 

 

Best Regards,

Icey

Anonymous
Not applicable

@Icey  I have used Lookupvalue and hasonevalue to get my requirement. 

 

Below are the measures which i have used. 

 

13 WKS = 

CALCULATE(SUM('TM Trend'[Sat_Day_Test_Count]), DATESBETWEEN('calendar'[Reference_Date], [CUS_CY13W_Start], [CUS_CY_BaseDate]))
 
CUS_CY13W_Start = LOOKUPVALUE('Calendar'[CUS_CY13W_StartDate], 'Calendar'[CUS_CY_Baseweek], [CUS_CY_BaseWeek_Selected])
 
CUS_CY_BaseWeek_Selected = IF(HASONEVALUE('Weeklist'[Base Week]), VALUES('Weeklist'[Base Week]), BLANK())
Icey
Community Support
Community Support

Hi @Anonymous ,

 

You can create another independent table for slicer.

Here are some similar posts:

Show last 3 months from selected month in Power BI;

Display Last N Month and Selected Month Data using Date Dimension in Power BI;

Display Last N Months & Selected Month using Single Date Dimension in Power BI.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , refer my blog on Week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

and these

Last 13 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
or
Last 13 period Sales =
Var _min = maxx(allselected('Date','Date'[Week Rank])
Var _max = maxx(allselected('Date','Date'[Week Rank]) -13
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=_min && 'Date'[Week Rank]<=_max))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.