Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
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, "" )
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, "" )
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |