weekly ranges in table power bi

[rebam12]

hi

i calculate weekly ranges for every month from date column.. but then problem is that when i drag formula in table then this shows only 1 value but when i drag Date column in table this shows all weekly ranges.. why i have to drag Date column in table.. i want weekly ranges in table wihtout dragging date column.. this is measure which i create

Week Range3 = 
VAR CurrentDate = MAX('Export'[Delivery Date (End)]) 
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1 -- Week starts on Monday
VAR WeekEnd = WeekStart + 6 -- Week ends on Sunday
RETURN 
    FORMAT(WeekStart, "MM/dd") & " - " & FORMAT(WeekEnd, "MM/dd")

this is the file ..in this file delivery date column is pasted

https://filetransfer.io/data-package/oVG8TRrN#link

Week Range3	Delivery Date (End)
06/24 - 06/30	6/26/2024 22:00
12/18 - 12/24	12/22/2023 19:30
01/29 - 02/04	2/1/2024 19:30
02/19 - 02/25	2/24/2024 18:30
04/15 - 04/21	4/17/2024 1:30
04/08 - 04/14	4/9/2024 20:45
06/17 - 06/23	6/21/2024 20:00
06/24 - 06/30	6/25/2024 21:15
01/08 - 01/14	1/12/2024 21:45
01/15 - 01/21	1/16/2024 21:45
01/15 - 01/21	1/17/2024 19:00
01/15 - 01/21	1/20/2024 21:00
10/28 - 11/03	10/28/2024 20:30
10/28 - 11/03	10/30/2024 15:30
09/30 - 10/06	10/4/2024 21:30
11/18 - 11/24	11/20/2024 17:00
11/25 - 12/01	11/25/2024
11/25 - 12/01	11/26/2024 21:15
11/04 - 11/10	11/5/2024 17:30
12/11 - 12/17	12/13/2023 23:00

this is the output i got  

[rajendraongole1 · Accepted solution]

Hi @rebam12  - you want to display unique weekly ranges without requiring the Date column in your table,

creates a static value for each row in the dataset using calculated column

Week Range Column =
VAR CurrentDate = 'Export'[Delivery Date (End)]
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1 -- Week starts on Monday
VAR WeekEnd = WeekStart + 6 -- Week ends on Sunday
RETURN
FORMAT(WeekStart, "MM/dd") & " - " & FORMAT(WeekEnd, "MM/dd")

You can create a distinct weekly range table and establish a relationship with your fact table

Weekly Ranges =
GENERATE(
CALENDAR(MIN('Export'[Delivery Date (End)]), MAX('Export'[Delivery Date (End)])),
VAR CurrentDate = [Date]
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR WeekEnd = WeekStart + 6
RETURN ROW(
"Week Start", WeekStart,
"Week End", WeekEnd,
"Week Range", FORMAT(WeekStart, "MM/dd") & " - " & FORMAT(WeekEnd, "MM/dd")
)
)

Use this table's Week Range column in your table visual.hope this works at your end.

[danextian · Accepted solution]

Hi @rebam12 

If your formula above is for a measure then you'll get only one value because measures don't have a row context. They are evaluated  based on the dimensions added together with it  to a viz. Unless you drag the date, the measure will reference the max date in your table (without filter) for the variable below.

VAR CurrentDate = MAX('Export'[Delivery Date (End)]) 

 Create a calculated column instead of a measure.

Week Range3 = 
VAR CurrentDate = 'Export'[Delivery Date (End)]
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1 -- Week starts on Monday
VAR WeekEnd = WeekStart + 6 -- Week ends on Sunday
RETURN 
    FORMAT(WeekStart, "MM/dd") & " - " & FORMAT(WeekEnd, "MM/dd")

Note: I removed MAX from CurrentDate variable as doing so will return the max date of the table instead of  the current row date.

[rajendraongole1 · Accepted solution]

Hi @rebam12  - you want to display unique weekly ranges without requiring the Date column in your table,

creates a static value for each row in the dataset using calculated column

Week Range Column =
VAR CurrentDate = 'Export'[Delivery Date (End)]
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1 -- Week starts on Monday
VAR WeekEnd = WeekStart + 6 -- Week ends on Sunday
RETURN
FORMAT(WeekStart, "MM/dd") & " - " & FORMAT(WeekEnd, "MM/dd")

You can create a distinct weekly range table and establish a relationship with your fact table

Weekly Ranges =
GENERATE(
CALENDAR(MIN('Export'[Delivery Date (End)]), MAX('Export'[Delivery Date (End)])),
VAR CurrentDate = [Date]
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR WeekEnd = WeekStart + 6
RETURN ROW(
"Week Start", WeekStart,
"Week End", WeekEnd,
"Week Range", FORMAT(WeekStart, "MM/dd") & " - " & FORMAT(WeekEnd, "MM/dd")
)
)

Use this table's Week Range column in your table visual.hope this works at your end.