Forum Discussion

Mahmoud_nabir's avatar
Mahmoud_nabir
Frequent Visitor
8 years ago
Solved

Date range to number weeks + weekday

I want to convert date range to number of weeks + weekday

Example

Date range : 4/9/2019. To 12/9/2019.

Result: 1 week + Tuesday + Wednesday

Can it be done as i have planned visits should be done during every day of week and i want to view the planned visits during a range of date . Planned visits configured by weekdays not a range.
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Here is a quick solution.  First thing you need is a Date Table that has a Day of the Week column with the day name included.  Then you can use this measure:

    Date Range Output = var startDate = FIRSTDATE('Date Table'[Date])
var endDate = LASTDATE('Date Table'[Date]) 
var dateLength = DATEDIFF(startDate, endDate, DAY)
var weeksMOD = MOD(dateLength, 7)
var weeksDIV = INT(DIVIDE(dateLength, 7))
var weekString = if (weeksMOD = 1, " week", " weeks")
var lastWeekday = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate)
var WeekdayM1 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 1)
var WeekdayM2 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 2)
var WeekdayM3 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 3)
var WeekdayM4 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 4)
var WeekdayM5 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 5)
RETURN
weeksDIV & weekString & SWITCH(
    weeksMOD,
    1, " + " & lastWeekday,
    2, " + " & WeekdayM1 & " + " & lastWeekday,
    3, " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    4, " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    5, " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    6, " + " & WeekdayM5 & " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    ""
)

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Here is a quick solution.  First thing you need is a Date Table that has a Day of the Week column with the day name included.  Then you can use this measure:

    Date Range Output = var startDate = FIRSTDATE('Date Table'[Date])
var endDate = LASTDATE('Date Table'[Date]) 
var dateLength = DATEDIFF(startDate, endDate, DAY)
var weeksMOD = MOD(dateLength, 7)
var weeksDIV = INT(DIVIDE(dateLength, 7))
var weekString = if (weeksMOD = 1, " week", " weeks")
var lastWeekday = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate)
var WeekdayM1 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 1)
var WeekdayM2 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 2)
var WeekdayM3 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 3)
var WeekdayM4 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 4)
var WeekdayM5 = LOOKUPVALUE('Date Table'[Day of Week], 'Date Table'[Date], enddate - 5)
RETURN
weeksDIV & weekString & SWITCH(
    weeksMOD,
    1, " + " & lastWeekday,
    2, " + " & WeekdayM1 & " + " & lastWeekday,
    3, " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    4, " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    5, " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    6, " + " & WeekdayM5 & " + " & WeekdayM4 & " + " & WeekdayM3 & " + " & WeekdayM2 & " + " & WeekdayM1 & " + " & lastWeekday,
    ""
)